distinct help

LucidParody

so i've got two tables:

photos:
id | url
1 | 1.jpg
2 | 2.jpg
3 | 3.jpg

etc

I have this query so far:

SELECT a.picture, b.name as picturename, b.url 
		FROM photo_comments a, photos b
		WHERE b.id=a.picture
		GROUP BY a.picture
		ORDER BY a.date DESC
		LIMIT 5

i've tried using the group by clause and i've tried using DISTINCT a.picture, but both times, when you have a picture that has been commented previously and if a user goes to comment on it again, the database uses the oldest comment to order by and not the newest comment. any ideas on how to fix this?

tekky

how about...

SELECT a.picture, b.name as picturename, b.url  

        FROM photo_comments a, photos b 
        WHERE b.id=a.picture AND a.date = MAX(a.date)
        GROUP BY a.picture 
        ORDER BY a.date DESC 
        LIMIT 5

LucidParody

i'm getting an "#1111 - Invalid use of group function" error...

tekky

ok then how about...

SELECT a.picture, b.name as picturename, b.url  

        FROM photo_comments a, photos b 
        WHERE b.id=a.picture
        GROUP BY a.picture HAVING a.date = MAX(a.date)
        ORDER BY a.date DESC 
        LIMIT 5

LucidParody

that still does not solve the problem. The query returns results in the same order as the original query I posted.

nemonoman

is date column a DATE type? or are you populating some sort of text column? Give specific instances of the data in the date column. If you are populating it with a string that is not in the form Year Month Day, you might be getting the correct sort even though the result is not what you mean or desire.

LucidParody

date is unix timestamp. specific instances of date:

1111707622
1111472070
1111108755

tekky

try using JOIN on picture and ID and then order by the timestamp