...

View Full Version : Finding MAX among DISTINCT



daXXon
03-07-2007, 11:14 PM
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

guelphdad
03-07-2007, 11:58 PM
show an example of your data along with expected output.

then show an example of the query you are trying.

daXXon
03-08-2007, 01:33 AM
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

Fumigator
03-08-2007, 03:52 AM
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)



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

guelphdad
03-08-2007, 05:32 AM
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 (http://codingforums.com/showthread.php?t=108984)

daXXon
03-08-2007, 04:03 PM
That was it Fumigator, thanks for the help -- really appreciated

guelphdad
03-08-2007, 04:34 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum