Multiple ways to give row number in sql based on different criteria

Multiple ways to give row number in sql based on different criteria

---

In sql you can provide row number based on different perspective to get different results.For that different syntax and different results are given below :-

1) Using Dense_rank with order by

DENSE_RANK() over (Order by P2.Store_Id)

Store_ID	Ro_Num
404	1
506	2
546	3
506	2
650	4
404	1
506	2
650	4

2) Using Rank with order by

RANK() over (Order by P2.Store_Id)

Store_ID	Ro_Num
404	1
506	1
546	1
506	2
650	1
404	2
506	3
650	2

3) Using Rank with partition by and order by

RANK() over (partition by SubCategoryID Order by ItemCode)

SubCategoryID	ItemCOde	Row_Num
1	A	1
1	C	3
1	B	2
1	D	4
2	F	2
2	K	3
2	C	1
3	J	2
3	L	3
3	E	1

3) Using Row_Number with order by

Row_Number() over (Order by P2.Store_Id)

Store_ID	Row_Num
404	1
506	3
546	6
506	4
650	7
404	2
506	5
650	8