0

I got the following table:

col1 | col2 | col3 | col4 | timestamp
-------------------------------------
  1  | ...  | ...  | ...  | 12:01
  1  | ...  | ...  | ...  | 12:40
  2  | ...  | ...  | ...  | 11:00
  2  | ...  | ...  | ...  | 13:00
  2  | ...  | ...  | ...  | 12:22
  3  | ...  | ...  | ...  | 16:00
  3  | ...  | ...  | ...  | 12:10

i want to get each row with max timestamp values grouped by column 1. this means the result have to be the following:

col1 | col2 | col3 | col4 | timestamp
-------------------------------------
  1  | ...  | ...  | ...  | 12:40
  2  | ...  | ...  | ...  | 13:00
  3  | ...  | ...  | ...  | 16:00

my following query works:

SELECT col1, MAX(timestamp)
FROM table
GROUP BY col1

but not this:

SELECT col1, col2, col3, col4, MAX(timestamp)
FROM table
GROUP BY col1
  • do columns 2 to 4 contain nothing? If not post some sample data – Matt Oct 04 '17 at 13:36
  • Possible duplicate of [Retrieving the last record in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Tab Alleman Oct 04 '17 at 13:56

2 Answers2

2

Give a row number based on partition by col1 column and in the descending order of timestamp column. And the select the rows having rn = 1.

Query

;with cte as(
    select [rn] = row_number() over(
        partition by [col1]
        order by [timestamp] desc
    ), *
    from your_table_name
)
select [col1], [col2], [col3], [col4], [timestamp]
from cte
where [rn] = 1;
Ullas
  • 11,450
  • 4
  • 33
  • 50
1

Well, you could get the maximum timestamp the way it works for you and then join with the initial table to get the other values, like this: 

select t.col1, t.col2, t.col3, t.col4, tmax.max_ts
from table t join   (
                            select col1, max(timestamp) max_ts
                            from table
                            group by col1
                         ) tmax on tmax.col1 = t.col1 and tmax.max_ts = t.timestamp
Rigerta
  • 3,959
  • 15
  • 26