Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding MAX among DISTINCT

    Hey guys,

    I'm pretty new to mysql, and I'm looking for some help.

    I have a database for photos -- the columns are id, id_num, date, and photo. The id_num ties certain records to specific clubs in a different database.

    The problem I'm having is I want to select DISTINCT(id_num) and then from within those results the MAX(date) available then ORDER BY date DESC.

    It seems nomader how I tweak my query I either will get mulitple results of the same id_num or cannont get them to list by date DESC.

    I'm sure this is a poorly worded question, but please bear with me. Anyway help would be appreciated.

    Luke

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    show an example of your data along with expected output.

    then show an example of the query you are trying.

  • #3
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Data -

    id -- id_num -- pic -- date
    1 1530771988 b1e6a26b.jpg 2007-03-03
    2 1530771988 6766e695.jpg 2007-01-02
    3 4984965455 7a856a00.jpg 2007-01-13
    4 9845656465 420a3b3f.jpg 2007-02-14
    5 1354896545 605440ca.jpg 2007-02-03

    Expected -

    id -- id_num -- pic -- date
    1 1530771988 b1e6a26b.jpg 2007-03-03
    4 9845656465 420a3b3f.jpg 2007-02-14
    5 1354896545 605440ca.jpg 2007-02-03
    3 4984965455 7a856a00.jpg 2007-01-13

    The query I've been bumbling around with is -

    SELECT DISTINCT(id_num), MAX(date) FROM photos GROUP BY date ORDER BY date DESC

    This causes the record 1530771988 to display twice amongst the other results whereas I'm looking for all id_num records to display only once in chronologically descending order using the most recent date found within the appropriate id_num records.

    Thanks for the repsonse, forgive my ignorance

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Just turn it around--- group it on the id_num and then select the max(date) out of each group. (Don't need to bother with the DISTINCT clause here)

    Code:
    SELECT id_num, MAX(date) FROM photos GROUP BY id_num ORDER BY 2 DESC

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    did you leave the row with id out by accident or is there a reason for it not appearing in your results?

    If you are trying to get the latest record for each id_num then see how that is done in this thread

  • #6
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That was it Fumigator, thanks for the help -- really appreciated

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    note that fumigator's query only works if you are selecting the one column. if you want all the columns returned as indicated then all of them except the max(date) have to be placed in the group by query. If you leave them out, you run the risk of running into incorrect data. See the manual for GROUP BY HIDDEN FIELDS if you are indeed returning more than one column but only have the id_num in your group by clause.

    See the link I have to the discussion above where you can return your results with a co-related subquery. It allows you to include as many columns as you need.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •