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