CODEWITHSUNDEEP
sqlsql-server-2012
user8001246
user8001246

Reputation:

get each row with max values of group

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

Upvotes: 0

Views: 45

Answers (2)

Rigerta
Rigerta

Reputation: 4039

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

Upvotes: 1

Ullas
Ullas

Reputation: 11556

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;

Upvotes: 2

Related Questions