Selecting Grouped Row

cpriest · #1 (**permalink**) 07-18-09, 08:35

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?

r937 · #2 (**permalink**) 07-18-09, 08:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech