SQL Query users set row number and last date

Question

I have a table something like this:

CREATE TABLE [dbo].[Test3](
[UserId] [int] NULL,
[RoleId] [int] NULL,
[lastDate] [date] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Test3]([UserId],[RoleId],[lastDate])
VALUES
(1,1,'2019-02-02'),
(1, NULL, '2019-02-02'),
(1, 2, '2019-02-03'),
(2, 1, '2019-02-05'),
(2, 1, '2019-02-02'),
(2, 1, '2019-02-02'),
(3, 3, '2019-02-02'),
(3, NULL, '2019-02-02')

How do I set row number and max lastdate a list of users who have the same "userid"? I need output should be look like this.

rownumber  userid   RoleId   lastdate
1          1        2       2019-02-03
2          1        1       2019-02-03
3          1        NULL    2019-02-03
1          2        1       2019-02-05
2          2        1       2019-02-05
3          2        1       2019-02-05
1          3        3       2019-02-02
2          3        NULL    2019-02-02

@GordonLinoff I assumed it was `roleid`, yeah. – The Impaler Jan 07 '20 at 15:22 — The Impaler, Jan 07 '20 at 15:22

score 2 · Answer 1 · edited Jan 08 '20 at 04:04

2

You can use row_number and max with window functions

I think this bellow code can help you.

SELECT ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY RoleId DESC) AS ROWNUMBER,
   userid,
   roleid,
   MAX(lastDate) OVER(PARTITION BY userId) AS LAST
FROM   test3

edited Jan 08 '20 at 04:04

Nguyễn Văn Phong

13,506
17
39
56

answered Jan 07 '20 at 15:17

Reza Jenabi

3,884
1
29
34

Add `NULLS LAST` in the `ORDER BY` of the first line. – The Impaler Jan 07 '20 at 15:19
My bad. SQL Server does not implement NULLS LAST. Workaround is https://stackoverflow.com/questions/5886857/sql-server-order-by-date-and-nulls-last – The Impaler Jan 07 '20 at 15:23
2

@TheImpaler there is no need for NULLS LAST since the sorting is `ORDER BY RoleId DESC`. – forpas Jan 07 '20 at 15:26
@forpas My bad, just realised by default SQL Server considers nulls smaller than any other value. No need for `NULLS LAST` in this case. – The Impaler Jan 08 '20 at 00:20

Yogesh Sharma · Answer 2 · 2020-01-07T15:24:51.703

1

You can use row_number() & max() with window function :

select t.*,
       row_number() over (partition by userid 
                          order by (case when roleid is null then 2 else 1 end), 
                                    roleid desc
                         ) as rowno,
       max(lastdate) over (partition by userid) as lastdate
from table t;

edited Jan 07 '20 at 15:24

answered Jan 07 '20 at 15:22

Yogesh Sharma

49,870
5
26
52

I always forget SQL Server does not implement `NULLS LAST`. Good one. – The Impaler Jan 07 '20 at 15:24
1

There is no need for CASE in ORDER BY. `roleid desc` is enough. – forpas Jan 07 '20 at 15:29

SQL Query users set row number and last date

2 Answers2