Grouping rows and adding sequence numbers in SQL SELECT statement without modifying table

Question

Assume that we have a table like this:

Date	User	Item
2021-01-01	A	X
2021-01-05	A	Y
2021-01-11	A	Z
2021-01-01	B	X
2021-01-16	B	Y
2021-01-01	C	X
2021-01-02	C	Y
2021-01-03	C	Z
2021-01-10	D	X
2021-01-15	D	Y

I want to group each user by its date and item and add sequence number DURING QUERY, NOT BY MODIFYING TABLE. For a user, the sequence number of the item with the first date should be first. The number should start all over again for each new user. I want to retrieve data like this:

Date	User	Item	Sequence
2021-01-01	A	X	1
2021-01-05	A	Y	2
2021-01-11	A	Z	3
2021-01-01	B	X	1
2021-01-16	B	Y	2
2021-01-01	C	X	1
2021-01-02	C	Y	2
2021-01-03	C	Z	3
2021-01-10	D	X	1
2021-01-15	D	Y	2

Is it possible? Can I retrieve data like that?

Thanks!

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2021-06-08 15:26:26 +00:00
Commented Jun 8, 2021 at 15:26 — Gordon Linoff
– Gordon Linoff, Commented Jun 8, 2021 at 15:26

Gordon Linoff · Accepted Answer · 2021-06-08 15:26:13Z

3

This is what row_number() does:

select t.*,
       row_number() over (partition by user order by date) as seqnum
from t;

answered Jun 8, 2021 at 15:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

aselimkaya Over a year ago

I also used row_number() but the key part that I missed is "partition by". Thanks!

Collectives™ on Stack Overflow

Grouping rows and adding sequence numbers in SQL SELECT statement without modifying table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related