CODEWITHSUNDEEP
sqlsql-servert-sql
OctavianWR
OctavianWR

Reputation: 227

Max sequence for every sequence SQL Server

I have this data on SQL Server

emp_id   date           seq
1        2020-10-01     1
1        2020-10-02     2
1        2020-10-05     1
1        2020-10-06     2
2        2020-10-01     1
2        2020-10-02     2
2        2020-10-03     3

I want to transform the data to max row for every sequence like this:

emp_id   date           seq
1        2020-10-02     2
1        2020-10-06     2
2        2020-10-03     3

I tried using max(seq) but the return only the max sequence for every employee, not for every sequence

Upvotes: 1

Views: 92

Answers (4)

forpas
forpas

Reputation: 164099

Use LEAD() window function to get the next value of seq of each row and fliter out the rows where the next value of seq is greater than the current one:

SELECT emp_id, date, seq
FROM (
  SELECT *, LEAD(seq) OVER (PARTITION BY emp_id ORDER BY date) next_seq
  FROM tablename
) t
WHERE seq > next_seq OR next_seq IS NULL

See the demo.

Results:

emp_id date seq
1 2020-10-02 2
1 2020-10-06 2
2 2020-10-03 3

Upvotes: 1

Charlieface
Charlieface

Reputation: 71638

You simply filter DENSE_RANK = 1

DENSE_RANK gives you tied results (same row-number for same order), ROW_NUMBER is non-tied

SELECT *
FROM (
    SELECT *,
        rn = DENSE_RANK() OVER (PARTITION BY t.emp_id ORDER BY seq DESC)
    FROM yourTable t
) t
WHERE rn = 1;

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Yet another option is WITH TIES in concert with the window function dense_rank()

Example

Select top 1 with ties * 
 From  YourTable
 Order By dense_rank() over (partition by emp_id order by seq desc)

Results

emp_id  date        seq
1       2020-10-02  2
1       2020-10-06  2
2       2020-10-03  3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

One method uses a correlated subquery:

select t.*
from t
where t.seq = (select max(t2.seq) from t t2 where t2.emp_id = t.emp_id);

Upvotes: -1

Related Questions