For generating random number for a list of data on a table:
For generating unique numbers for a list of data on table we can use row_number method followed by order by of maintaining order on the basis of column name we would like to order from. A simple query can be as below and image with the query execution is below too.
select row_number() over (order by customername) as ordernumber, * from tblcustomer
For generating random number for a list of data on a table and group the ordering by any column value:
This is the condition where we need to use the partition method:
simple syntax can be
select row_number() over (partition by department order by customername) as departmentwisenumbering, * from tblcustomer
For generating unique number for individual customer as per the above example:
Similar to row_number but if order by column has same entry it will create different number for same customer so we rank function here.
select rank() over (order by customername) as uniqueuser, * from tblcustomer
For generating unique number for individual customer but with sequential number which lacks in rank function:
With the use of rank function though each function was assigned a unique id and in the above example the rajendra entry was assigned 1 as the id but when it went to the next sequence number it went to 3 i.e. it skipped number 2 so to over come it we use another function dense_rank
select dense_rank() over (order by customername) as uniqueuser, * from tblcustomer
So, this is all about these functions.
Screenshots: QuestPond YouTube Video