Announcement

Announcement Module
Collapse
No announcement yet.

Order By Before Group By

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order By Before Group By

    I'm trying to GROUP BY a column and have the row with the latest timestamp to be returned. Example:

    TABLE Test

    id | text | timestamp
    1 , 'A' , 2008-03-01
    1 , 'B' , 2008-03-26

    SELECT *
    FROM Test
    GROUP BY id

    RESULTS:
    1 , 'A' , 2008-03-01

    I need the results to be the row with the latest timestamp:
    1 , 'B' , 2008-03-26

    I know i can nest queries to do this but this will result in temporary tables which will not be optimized. Is it possible to Order By before the Group or get this result using another method?

    Thanks

  • #2
    This is what you want:

    SELECT id, MAX(timestamp)FROM TestGROUP BY id

    Read about the group by functions here:
    http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.ht ml

    Comment


    • #3
      Thanks for the reply but that will not work as it will not return the row with the greatest timestamp only the greatest timestamp for all the rows.

      I did find the answer i was seeking at MySQL. This type of query is referred to as a Group-wise Maximum query.

      http://dev.mysql.com/doc/refman/5.0/en/example-maximum-colum n-group-row.html

      In Google Groups some have labelled it a Strawberry Query.

      Comment


      • #4
        hi jadent send that code

        Comment


        • #5
          I know it's a bit late, but still useful for newcomers.

          If there's a PK or a UNIQUE constraint (on 1 or many fields), that may work :

          SELECT Test.*FROM TestINNER JOIN ( SELECT MAX(id) id FROM Test GROUP BY gid) s ON s.id = Test.id

          ... where id is PK of table 'Test' and gid, the field to group by.

          Comment

          Working...
          X