View Full Version : Getting distinct values

09-23-2011, 07:30 AM
I am trying to figure out how I can pull the latest values of a distinct type from a table.

For example, if I had a table of posts, with the columns postID (int), threadID (int), title (varchar, 50), and datePosted (datetime). How could I get the latest post from each threadID?

I tried SELECT DISTINCT threadID, postID, title, datePosted FROM posts ORDER BY datePosted DESC, but it didn't work. The only other way I know to do it is to make a query table that reverses the order, then group by threadID, but that seems a bit convoluted. I tried doing a ORDER BY then GROUP BY, but it failed, and only worked when I grouped first, then ordered.

Any other way to do it? Or is the dynamic query table the only way?

09-23-2011, 10:16 AM
SELECT max(postID) as maxId FROM posts GROUP BY threadID;

SELECT threadID, postID, title, datePosted FROM posts
INNER JOIN latestPostByThread on postID=maxID

09-23-2011, 10:51 AM
for something similar I have:

Select ...
select max(date) as d , id
FROM table
group by id
) as t1 join (back to original table) table on t1.id = table.id ..

result of that: http://www.agrozoo.net/jsp/gallery_last_comments.jsp
If you click on bee & scroll down you will see 'test' is last comment of two, and only last is shown.

Mybe having view inbetween is a bit faster, but not much I think, don't know ...

09-23-2011, 04:56 PM
Ah, ok, so we are on the same page then.

I actually did already setup a view for it, which just extracts the last post for each thread, which I use to join, just like you did abduraooft.

And your technique BubikolRamios is what I meant by "query table".

I was curious if I had missed something in terms of using DISTINCT, or there was a third technique I didn't know. Either way, thanks guys!

09-23-2011, 05:23 PM
I was curious if I had missed something in terms of using DISTINCT
Yes, the Distinct keyword applies to all the columns in the Select clause and hence it generally works well when there's less number of columns. In your case, all the rows will be Distinct.

09-23-2011, 05:42 PM
Ah, so doesn't work for what I'm trying to do. Thanks.