RANKING FUNCTIONS IN SQL SERVER

Dept Table

Ranking function

These are built in functions that are used to generate rank for the row based on the values of a column in the table.

Ranking functions must  use over “order by clause”

Ranking functions available in sql server are as follows

Rank()

Dense_Rank()

Row_number()

Rank()-It is used to generate the rank by skipping next rank when more than one row share the same rank

dense_rank() - It is used to generate the rank, and it generates sequential no for the rows in same order, as they are retrieved from the table.

Row_number()

This will not generate a rank and it generates sequential number for the rows in same order as they are retrieved from the table.

rank  dense_rank

1      1

2      2

2      2

4      3

4     3

4     3

7     4

Display empno, empname ,salary and deptno along with the rank for the employee based on the sal by giving the salary  one for the highest salary of the emp deptwise

select empno,ename,sal ,deptno,rank() over(partition by deptno order by sal desc) as rank,

DENSE_RANK() over(partition by deptno order by sal desc) as denserank,

ROW_NUMBER() over(partition by deptno order by sal desc) as rownum from emp

Output:

Display empno,ename and salaries along with the rank for the employee based on the salary of the employee with the highest salary employee given rank1

select empno,ename,sal ,deptno,rank() over(order by sal desc) as rank,

DENSE_RANK() over(order by sal desc) as denserank,

ROW_NUMBER() over(order by sal desc) as rownum from emp

Output:

7839   KING   5000.00 10  1   1   1

7902   FORD   3000.00 20  2   2   2

7788   SCOTT  3000.00 20  2   2   3

7566   JONES  2975.00 20  4   3   4

7698   BLAKE  2850.00 30  5   4   5

7782   CLARK  2450.00 10  6   5   6

7499   ALLEN  1600.00 30  7   6   7

7844   TURNER 1500.00 30  8   7   8

7934   MILLER 1300.00 40  9   8   9

7521   WARD   1250.00 30  10  9   10

7654   MARTIN 1250.00 30  10  9   11

7876   ADAMS  1100.00 20  12  10  12

7900   JAMES  950.00 30  13  11  13

7369   SMITH  800.00 20  14  12  14

While using the ranking functions within the order clause you can use partition by along with order by for dividing the rows of the table into partitions and generate rows for each partition separately and in this case partition must be written first and then order by.

Note: dense_Rank function won’t generate gaps

Rank function generate gaps

Display ename,salary with rank ranking should be based on salary?

select ename,sal,rank() over(order by sal desc) as rank from emp

select ename,sal,dense_Rank() over(order by sal desc) as denserank from emp

Based on the salary first then rank should be on hire date

select ename, sal ,DENSE_RANK() over(order by sal desc,hiredate) as rank  from emp

Find the rank of the employee with in the dept based on their salaries

Select ename, sal, deptno, dense_Rank() over(partition by deptno order by sal desc) as rank from emp

COMMON TABLE EXPRESSION(NEW FEATURE IN 2008 SQL SERVER)

COMMON TABLE EXPRESSION (NEW FEATURE IN 2008 SQL SERVER)

Are used to solve some complex queries that are not possible to solve with simple select statement.

CTE will be treated as temporary table created from the result of a select statement on which you can perform Insert, Update, and Delete.

Syntax:

With <ctename> as

(<select statement>)

<select insert/update/delete statement>

Examples:

Emp table:

Dept Table

Display first 5 rows from the table emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno) as rownum from emp)

select  * from empcte where rownum<=5

Display last 5 rows from table emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno desc) as rownum from emp)

select  * from empcte where rownum<=5

Display 5^th, 7^thand 10^th row from emp

with empcte as

(select * ,ROW_NUMBER() over(order by empno desc) as rownum from emp)

select  * from empcte where rownum in(5,7,10)

Find total and average salary paid to employee deptwise only for the department in which there are more than 2 employees without using havingclause

with empcte as

(select deptno,sum(Sal) as totalsalary, avg(SAl) as averagesal, count(*) as empcount from emp

group by deptno)

select * from empcte where empcount>2

or

with empcte (deptno,totalsalary,averagesalary,empcount)

as

(select deptno,sum(Sal),avg(Sal),count(*) from emp group by (deptno))

select * from empcte where empcount>2

Delete Duplicate rows from table emp

First I am creating a table emp1 and then inserting rows.

select * into emp1 from emp

write this insert syntax three times so that multiple records will be added, as we need to delete duplicate records from the table.

insert emp1 select * from emp

insert emp1 select * from emp

insert emp1 select * from emp

now we will delete duplicate records.

with empcte as

(select *,rank() over(partition by empno,ename,job,mgr,hiredate,deptno order by newid())

as rank from emp1)

delete empcte where rank>1

Explanation:

Create a table emp1 from table emp as follows

Select * into emp1 from emp

2 duplicate the rows in table emp1 by inserting rows from emp table into it as follows

insert emp1 select * from emp

to delete duplicate rows from the table emp1 

write the statement as follows

with empcte as

(select *,rank() over(partition by empno,ename,job,mgr,hiredate,deptno order by newid())

as rank from emp1)

 delete empcte where rank>1

GROUP BY CLAUSE IN SQL SERVER

Dept Table

Group by:

Is used to divide the rows of a table into groups and then calculate aggregate on each group separately.

Syntax:

Select * /<collist> from <tablename>

Where <condition>

Group by <collist>

[Order by…..]

               

Example:

Display max salaries for each department.

select deptno, max(sal) from emp group by deptno

Find total and average salary paid to employee?

select deptno, sum(sal) , avg(sal) from emp group by deptno

Find total salary paid to employee job-wise in each department.

selectdeptno,job,sum(sal) from emp group by deptno,job

Display max sal, min sal , sum sal, no of employees working in each job

selectjob, max(sal) maxsal, min(sal)  minsal, sum(sal) sumsal, count(*)  noofemp

fromemp group by job

Display no of employees joined in each year

selectyear(hiredate) year, count(*) noofemp from emp group by year(hiredate)

No of employees joined in each day.

selectdatename(dw,hiredate) day, count(*) noofemp from emp group by datename(dw,hiredate)

To find the highest salaries for each department in it for each job

selectdeptno,job ,max(sal) from emp group by deptno,job

To find no of employees working for each department

selectdeptno,count(*) from emp group by deptno

Display maxsalaries for each department whose department number is equal to 10 or 30

select deptno, max(sal) maxsal from emp where deptno in(10,30) group by deptno

Rule:

 A rule to follow while using group by is

1 Every column in select other than aggregates must be in group by

HAVING CLAUSE IN SQL SERVER

Dept Table

Having clause

To filter data before group by we use where clause

To filter data after group by we use clause having

In condition if there is no aggregate function then use where clause

In condition if there is aggregate function then use having clause

Display maxsalaries for each dept. where deptno is equal to 10 and 30 and maxsal>3000

Select deptno, max (sal) from EMP where deptno in (10, 30) group by deptno having max (Sal)>=3000

Find total and avg sal paid to EMP deptwise only for the dept in which they are more than 2 EMP

Select deptno, sum (sal), avg (sal), count (*) from EMP group by deptno having count (*)>2

Find the no of EMP working for each dept only if no greater than 3

Select deptno, count (*) from EMP group by deptno having count (*) >3

Find the highest and lowest sal paid to EMP jobwise only for the job whose avg sal more than 2000

select job, avg(sal),max(Sal) ,min(Sal) from  emp group by job having avg(Sal)>2000

WRITE a query to find no of clerks working in each dept if the count is greater than 1

Select deptno, count (*) from EMP where job='clerk' group by deptno having count (*)>1

Display no of EMP joined in each year when year=1981 or 1982 and no of EMP >2

select year(hiredate) ,count(*) from emp where year(hiredate) in (1981,1982) group by year(hiredate) having count(*)>2

Display dept wise sum salaries and within the dept jobwise

Select deptno, job, sum (sal) from EMP group by deptno, job order by deptno, job

Write a query to find no of clerks working in each dept?

Select deptno, count (*) from EMP where job=’clerk’ group by deptno

OVER(PARTITION BY) IN SQL SERVER

Dept Table

over partition by

A drawback of group by is that it can display the data from only the columns that are in group by

To overcome this problem sql server provides over(partition by---) 

Similarly to group by over partition is use to divide the rows into partitions and then calculate 

Aggregates on each partitions separately.

Syntax:

Select * /<collist>,<aggregate> over(partition by <collist>).  <aggregate> over(partition by <collist>) from <tablename>

[where <condition>   [order by  …..]

Examples:

Find total salary paid to employee dept-wise while displaying complete details of emp

Select *, sum (sal) over (partition by deptno) from EMP

Find the highest and lowest salary paid employee 

jobwise while displaying complete details of emp

Select *,max(sal) over(partition by job), min(sal) over(partition by job) from emp

Find the total salary paid to emp jobwise in each dept while displaying complete details

Select *, sum(sal) over(partition by deptno, job) from emp