SQL Query: how to sort and display row_number()

March 23, 2023

I am trying to calculate the number of rows grouped by two columns (User_location & Map_id), but it’s not giving me the value that I want to see. Its only allowing me to group by one column.

Session_id	User_location	Map_id	loc_div
XD001	Delta 1	A1	loc010
XD002	Delta 1	A1	loc020
XD003	Delta 1	A1	loc040
XD004	Delta 1	A2	loc050
XD005	Delta 1	A2	loc060
XD006	Delta 2	A1	loc070
XD007	Delta 2	A2	loc080
XD008	Delta 2	A2	loc090
XD009	Delta 2	A1	loc030

Used Code:

SELECT ROW_NUMBER() OVER(PARTITION BY User_location
    ORDER BY Map_id ASC) AS Row_Num, 
    Session_id, User_location, Map_id, loc_div 
FROM table

This is what I am getting.

Row_num	Session_id	User_location	Map_id	loc_div
1	XD001	Delta 1	A1	loc010
2	XD002	Delta 1	A1	loc020
3	XD003	Delta 1	A1	loc040
4	XD004	Delta 1	A2	loc050
5	XD005	Delta 1	A2	loc060
1	XD006	Delta 2	A1	loc070
2	XD009	Delta 2	A1	loc030
3	XD007	Delta 2	A2	loc080
4	XD008	Delta 2	A2	loc090

Trying to achieve separate row numbers grouped by user_location and map_id like below

Row_num	Session_id	User_location	Map_id	loc_div
1	XD001	Delta 1	A1	loc010
2	XD002	Delta 1	A1	loc020
3	XD003	Delta 1	A1	loc040
1	XD004	Delta 1	A2	loc050
2	XD005	Delta 1	A2	loc060
1	XD006	Delta 2	A1	loc070
2	XD009	Delta 2	A1	loc030
1	XD007	Delta 2	A2	loc080
2	XD008	Delta 2	A2	loc090

>Solution :

You may add either the Session_id or the loc_div to the row number partition. Assuming the latter, you may try:

SELECT ROW_NUMBER() OVER (PARTITION BY User_location, loc_div
                          ORDER BY Map_id) AS Row_Num, 
       Session_id, User_location, Map_id, loc_div 
FROM yourTable
ORDER BY User_location, Map_id, loc_div;