Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: How to limit records

    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?

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    SELECT distinct t1.ID_TOPIC, t1.subject
    FROM messages AS t1
    ORDER BY t1.posterTime DESC
    LIMIT 10
    Mike

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Here be some data:

    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

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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ID_TOPIC
         , subject
         , MAX(posterTime) AS maxtime
      FROM messages
    GROUP 
        BY ID_TOPIC
    ORDER 
        BY maxtime DESC LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •