How to limit records

Frunkie · #1 (**permalink**) 11-29-08, 02:26

Admittedly I haven't been using much database code lately and something that I think I would have been able to handle on my own before, has me stumped today.

What I need are the 10 latest records, differentiated by a UNIX timestamp column (t1.posterTime).

This is the code as I thought it should be. I get correct results when I remove the GROUP BY but then get dupe rows. What I need are all distinct rows and then order them by the latest UNIX stamp. Easy I know, but I'm stuck. Should I be using a self join for this?

Quote:

SELECT t1.ID_TOPIC, t1.subject
FROM messages AS t1
GROUP BY t1.ID_TOPIC
ORDER BY
t1.posterTime DESC
LIMIT 10

Thanks for any help!

Frank

mike_bike_kite · #2 (**permalink**) 11-29-08, 04:49

Code:

SELECT distinct t1.ID_TOPIC, t1.subject
FROM messages AS t1
ORDER BY t1.posterTime DESC
LIMIT 10

Mike

Frunkie · #3 (**permalink**) 11-29-08, 04:57

Hey Mike, thanks for the reply. It doesn't work. I had that exact same code but it is not giving me the MAX() timestamp. It will group the rows but again, will not give me the most recent.

Something tells me I need a self join but I can't recall what exactly I need to do to get the rows I need.

Frunkie · #4 (**permalink**) 11-29-08, 05:14

Here be some data:

Quote:

80 Re: Test1 1227941017
117 Re: Test2 1227924025
117 Re: Test2 1227921294
117 Re: Test2 1227914479
9 Re: Test3 1227900437

Notice that the first column has 3 dupes (117). The far right column is the UNIX stamp. What I need is the max() timestamp column of any duplicated row. This sample data was ran without the GROUP BY clause. If I use the GROUP BY clause the timestamp order is not correct.

So here is what I am after

Quote:

80 Re: Test1 1227941017
117 Re: Test2 1227924025
9 Re: Test3 1227900437

r937 · #5 (**permalink**) 11-29-08, 07:13

Code:

SELECT ID_TOPIC
     , subject
     , MAX(posterTime) AS maxtime
  FROM messages
GROUP 
    BY ID_TOPIC
ORDER 
    BY maxtime DESC LIMIT 10

mike_bike_kite · #6 (**permalink**) 11-29-08, 08:43

Quote:

It doesn't work. I had that exact same code but it is not giving me the MAX() timestamp

Afraid I don't have your tables to actually try the SQL and I only have your rough description to work with so that was the best I could offer. Things seem a bit better defined now and I assume Rudy's code works so all's well that ends well.

Frunkie · #7 (**permalink**) 11-29-08, 16:40

That did the trick. I was close in my code but not close enough. I did an ORDER BY as well but not on the MAX() column which is what made all the difference.

As always, thanks Rudy! Thanks to you too Mike.