Ranking functions in sqlserver

Ranking functions are used to generate a rank for the row based on values of a column of that row
RANK():Is used to generate the rank based on the values of a specified column.Here i will show an example by generating a rank for the employee based on the salary

SELECT empno,ename,sal,RANK() OVER(ORDER BY sal DESC)From eemp

While using the ranking functions it is compulsory to use OVER clause with ORDER BY rank will be generated based n the values in the column that is specified in order by clause with in the over clause.The RANK function will skip the next immediate ranks when more than one row share the same rank

DENSE_RANK():
This function will also generate a rank same as the rank function based on the values of column .That is specified in order by clause of over clause but unlike the rank function it will skip the next immediate ranks when more than one row share the same rank
Example:

SELECT empno,ename,sal,DENSE_RANK() OVER(ORDER BY salDESC)From eemp

While using the ranking function you may want to generate the rank by dividing the rows in to groups.In this case you need to use partitions by with in the over clause along with order by and partion by must be written first

SELECT empno,ename,,Deptnosal,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY salDESC)From eemp

TOP n:Top n clause is used to retrieve only top n rows from the rows retrieved by select statement

SELECT TOP 4 cusno,cname,sales from emp orderby sales DESC

Top n clause supports the keyword "PERCENT" using which you can retrieve TOP n percent of rows instead of top n rows

Top n clause also supports the key word "WITH TIES" and this case it will retrieve all the rows in table that contain the same value is the column that is specified in order by same as last row retrieved by the top n clause

SELECT TOP 10 percent WITH TIES cno,cname,sales from customers ORDER BY sales DESC

Actually the above query has to written only the rows as the total no of rows in the table is 15 and we specify top 10 percent but displays 5 rows because there are 4 customers drawing the sales 4000 which is the sales of last employee retrieve with TOP 10 percent option