help with DISTINCT query

breaks when selecting more then one column

[nfs2]

On my blog site, i have a list of the 5 most recently updated blogs. The list should be distinct, so nobody should be on it twice..
To do this, i've been using DISTINCT in my sql query, and its been working great. Up untill now i was only selecting the blog_id so there has been no problems.

Now im trying to display the time of the entry, so now i have to select blog_id and entry_time from the blog table. This breaks the DISTINCT and people can appear on the list more then once.

My query is similar to the one below;

$result=mysql_query("SELECT DISTINCT blog_id, entry_time FROM blog_entries ORDER BY entry_id DESC LIMIT 5");

Any idea how i can get both the blog_id as well as the entry_time while keeping the results distinct?

[Dijkgraaf]

Something along the lines of

SELECT blog_id, max(entry_time)
FROM blog_entries
GROUP BY blog_id
ORDER BY entry_time DESC
LIMIT 5

[nfs2]

Hi

I tried using that code, but it doesn't get the latest entries. It actually seems like a random 5 entries.

Any other ideas?

Thanks for your help

[nfs2]

Ok i figured out that code is grabbing the LOWEST entry_id from a user. There are distinct results, but not the right results.

I need the highest entry_id from each user, because entry_id is unique and auto increments

[nfs2]

Anyone?

[nfs2]

Ah, i fixed it. Thanks for your help :)

[tryant4d]

If it's your database, you might try setting up a new table that INSERTs a blog_id and entry_time each time the user enters. If the blog_id exists, you UPDATE rather than INSERTing.

Then you simply

SELECT * FROM your_new_table

Tom

[nfs2]

Thanks, but the following worked great with my database

SELECT blog_id, entry_time FROM blog_entries GROUP BY blog_id ORDER BY entry_id DESC LIMIT 5

[Andrew Bassett]

Shouldn't you be ordering by entry_time desc? Just a nitpick.

[nfs2]

Well i dont know why i should.. I mean entry_id is unique, and the highest number is the latest entry.. Seems to make sence to me that i should order by that if i want to show the latest entries..

Plus its possible that 2 entries are made in the same second, so time isnt neccisairly unique.