Most of the time we use ranking function like ROW_NUMBER() when required to generate a unique number sequence. But there are also other ways to accomplish the same thing without using ranking function. I will tell you 3 various ways to generate the unique number sequence including ranking function.

Let’s create a demo table with some dummy data:

  1. CREATE TABLE Months(MonthId INT, Mon_Name VARCHAR(20))
  2. GO
  3. INSERT INTO Months
  4. SELECT 101, 'January'
  5. UNION ALL
  6. SELECT 102, 'February'
  7. UNION ALL
  8. SELECT 103, 'March'
  9. UNION ALL
  10. SELECT 104, 'April'
  11. UNION ALL
  12. SELECT 105, 'May'
  13. UNION ALL
  14. SELECT 106, 'June'
  15. UNION ALL
  16. SELECT 107, 'July'
  17. UNION ALL
  18. SELECT 108, 'August'
  19. UNION ALL
  20. SELECT 109, 'September'
  21. UNION ALL
  22. SELECT 110, 'October'
  23. UNION ALL
  24. SELECT 111, 'November'
  25. UNION ALL
  26. SELECT 112, 'December'
  27. Here, are the below 3 different methods to generate the unique numbers.
  28. --Method 1
  29. SELECT MonthId, Mon_Name, ROW_NUMBER() OVER(ORDER BY Mon_Name) rnum
  30. FROM Months
  31. --Method 2
  32. SELECT MonthId, Mon_Name, COUNT( * ) OVER(ORDER BY MonthId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rnum
  33. FROM months
  34. --Method 3
  35. SELECT MonthId, Mon_name, (SELECT COUNT( * ) FROM months m1 WHERE m1.MonthId <= m2.MonthId) as rnum
  36. FROM months m2
  37. ORDER BY rnum

SQL Server

After executing these queries we will get the sequence of unique numbers. We can choose any method according to our choice to generate the numbers sequence.

That’s all for today folks. Keep learning.