SQL - Useful system functions

1. Coalesce

--COALESCE ( expression [ ,...n ] )

 --Returns value from first not null column from the provided columns

  select Coalesce(FirstName,MiddleName,LastName) from tb_userdesc

2. Row_Number

 --Row_number()

 --Sorted on FirstName and given serial number

 select Row_number() over (order by Firstname) as SNo, userid, FirstName, Address_Type from tb_userdesc

 -- Records grouped on values of Address_Type then these group has sorted on UserId with row number restarted

 select Row_number() over (PARTITION by Address_Type order by UserId) as SNo, userid, FirstName, Address_Type from tb_userdesc

3. Rank

 --Rank()

 -- Generates sequence like Row_number, gives same number for same values in order by column. But for next value uses the number that actually returned by Row_number

 select rank() over (order by FirstName) as SNo, userid, FirstName, Address_Type from tb_userdesc

4. Dense_Rank

 --dense_rank()

 --Generates sequence like Row_number, gives same number for same values in order by column. For next value uses the next number in sequence

 select dense_rank() over (order by FirstName) as SNo, userid, FirstName, Address_Type from tb_userdesc