Ranking by Rank() function then what about Row_NUBER() TIP #37

 

In last post we have discussed Row_Number() function. In similar way we have RANK function. By the name it is clear that we provide  some kind of ranking for rows.

It syntax is exact same like ROW_NUMBER().

Now the question comes in mind if it is same then why SQL SERVER introduce new function.

so there is slightly difference

ROW_NUMBER() will generate sequential number regardless of  duplicate rows in partition.

While  RANK() will generate sequential number for unique row in partition and use same sequence for duplicate and skip sequence which lies between duplicate.

For example in  below query we want to know total dues rank according to  territory and  modified date.

so we write following

SELECT TerritoryID,TotalDue,ModifiedDate,SalesOrderNumber ,RANK() OVER (Partition by ModifiedDate,TerritoryID ORDER BY TotalDue) ranks FROM [Sales].[SalesOrderHeader]

When we run it we get following result

rankk

enjoy !!!

RJ

Row_Number() function for providing sequence number as per your wish TIP #35

 

Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.

Lets understand this by following example

In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write following query

SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName  FROM [Person].[Person]

When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO

Row_Number

Lets understand the query in this we are getting sequence or Row number order by Last name.

There is one more option with ROW_NUMBER which is partition by

lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query

SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName  FROM [Person].[Person]

See below snap when we run we get following result

row_number_Over

If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.

I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.

Enjoy!!

RJ

5 Ways in 5 minutes to find 2nd highest Salary Or 2nd minimum Salary

Hello friends,

Today’s post is for all new bibes going for interview and scarred of sql query question how to determine 2nd highest or 2 nd lowest salary.

Or we can say nth highest or lowest salary for this I will provide you some basic Sql suppose.

DECLARE @tblEmployeeSalary TABLE(lngEmployeeId INT, strEmpName VARCHAR(100), fltBasicSalary FLOAT)

INSERT @tblEmployeeSalary SELECT 1,’RAJAT’,345345

INSERT @tblEmployeeSalary SELECT 2,’ASHISH’,76845

INSERT @tblEmployeeSalary SELECT 3,’KAPIL’,234545

INSERT @tblEmployeeSalary SELECT 4,’KAMLESH’,74564

INSERT @tblEmployeeSalary SELECT 5,’RAVI’,56756456

INSERT @tblEmployeeSalary SELECT 6,’SHIV’,75675

INSERT @tblEmployeeSalary SELECT 7,’MONICA’,76566

INSERT @tblEmployeeSalary SELECT 8,’PIYUSH’,58776

INSERT @tblEmployeeSalary SELECT 9,’KUNAL’,345567

INSERT @tblEmployeeSalary SELECT 10,’MANISH’,76766

1) The below query is simplest query for finding 2nd minimum salary.if you want 2nd maximum salary then

you have to just change the order by fltbasicSalary desc.

SELECT MAX (fltBasicSalary)

FROM @tblEmployeeSalary

WHERE fltBasicSalary IN (SELECT DISTINCT TOP 2 fltBasicSalary

FROM @tblEmployeeSalary

ORDER BY fltBasicSalary ASC)

2) This bit complex then first one in that you have to change condition of where clause for min /max

according to your requirment. if you want nth highest or lowest salary then just replace 2 by

that particular number which you want.

SELECT MIN(fltBasicSalary)

FROM @tblEmployeeSalary e1

WHERE 2 <=(SELECT COUNT(*)

FROM @tblEmployeeSalary e2 WHERE e1.fltBasicSalary >= e2.fltBasicSalary);

3) This is simple one but restriction is it require SQL SERVER 2005 it will not work on SQL server 2000

actually in sqlserver 2005 we have Rank, Row_Number(), Dens_Rank() function by utilizing then we can ,

give a row rank or number. i am using that concept.

 

SELECT tmp.fltBasicSalary

FROM (SELECT DISTINCT fltBasicSalary ,

ROW_NUMBER()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber

FROM @tblEmployeeSalary)tmp

WHERE tmp.intRowNumber = 2

4) In same way we can use Rank and dens rank

SELECT tmp.fltBasicSalary

FROM (SELECT DISTINCT fltBasicSalary ,

RANK()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber

FROM @tblEmployeeSalary)tmp

WHERE tmp.intRowNumber = 2

5) This is another way of getting max or min salary by a group by statement

SELECT TOP 1 fltBasicSalary

FROM (SELECT TOP 2 fltBasicSalary

FROM @tblEmployeeSalary

GROUP BY fltBasicSalary ORDER BY fltBasicSalary ASC) AS tmp ORDER BY fltBasicSalary DESC

I hope you people like it.

if you find any problem in that feel free to ask…

enjoy SQL server.

enjoy programming

Thanks

Rajat