Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Question Unanswered: Selecting Grouped Row

    I'm trying to figure out how to choose the row that a grouped rows value will represent.

    Input Rows:
    Code:
    Table1
    | ID | JoinID | SourceID | Timestamp             |
    --------------------------------------------------
    | 1  | 1      | 1        | '2009-07-16 11:48:32' |
    | 2  | 1      | 7        | '2009-07-17 14:32:11' |
    | 3  | 1      | 3        | '2009-07-18 07:14:47' |
    | 4  | 2      | 6        | '2009-07-14 16:12:23' |
    | 5  | 2      | 4        | '2009-07-18 10:32:07' |
    Result Rows Desired:
    Code:
    Table1
    | ID | JoinID | SourceID | Timestamp             |
    --------------------------------------------------
    | 3  | 1      | 3        | '2009-07-18 07:14:47' |
    | 5  | 2      | 4        | '2009-07-18 10:32:07' |
    You see I want to do something like this:
    Code:
    SELECT *
    FROM Table1
    GROUP BY JoinID
    But specifically I want the 'rolled up' row to be represented by the row with the highest timestamp. So effectively the JoinID, SourceID and ID are all the values in the row with the highest Timestamp value.

    Any ideas on the proper way to do this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cpriest
    But specifically I want the 'rolled up' row to be represented by the row with the highest timestamp.
    there are many ways to do it

    here are eleven ways: ~jk groupwise max

    and here are five more (written for SQL Server, including analytical/windowing solutions which don't apply to mysql): Less Than Dot - Blog - Including an Aggregated Column's Related Values

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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