Max sequence for every sequence SQL Server

Question

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

forpas · Accepted Answer · 2021-06-29 11:16:06Z

1

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

answered Jun 29, 2021 at 11:16

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2021-06-29 01:54:11Z

1

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

answered Jun 29, 2021 at 1:54

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Comments

Charlieface · Accepted Answer · 2021-06-29 11:05:42Z

1

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;

answered Jun 29, 2021 at 11:05

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

1 Comment

Gordon Linoff Over a year ago

@OctavianWR . . . If you are going to use window functions (which are unnecessary) then this is the better and clearer approach.

Gordon Linoff · Accepted Answer · 2021-06-29 01:47:11Z

-1

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);

answered Jun 29, 2021 at 1:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Max sequence for every sequence SQL Server

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related