Some tricky Queries in database | Techbirds
Posted on: December 6, 2013 /
Categories: Tutorials / Author Name: Ankit Shukla
Hi Everyone,
In database normally we stuck over some problem like :
1 . To retrieve the n max column field or record from table .
So here is generic formula :
Select max(column_name ) from table t1 where salary Not In
(select distinct top(n-1) column_name from table t1 ORDER BY column_name DESC ) ;
Example :
-To retrieve the 3 max salary from employee table .
Select max(salary) from employee where salary Not In
(select distinct top 2 salary from employee order by salary DESC );
2. To retrieve the n max column field or record from table .but there is a condition
To improve performance USE WINDOWS FUNCTION with PARTITION BY .
1. RANK :
Rank assign unique no for each row starting with 1 , except for those who have duplicates
Values assigning the same rank and a gap appear for next row assignment.
2 .ROW_NUMBER:
ROW_NUMBER assign unique no for each row starting with 1 .For rows having duplicate
values, numbers are arbitrary assigned .
3 .DENSE_RANK:
DENSE_RANK assign unique no for each row starting with 1 except for those who have duplicates
Values assigning the same rank but a gap will not be appear for next row assignment.
: This is the basic difference between DENSE_RANK & RANK.
Example :
| ID | NAME | DESCRIPTION | FEES |
| 1 | JAVA | – | 94.99 |
| 2 | .NET | – | 91.22 |
| 3 | SAP | – | 95.92 |
| 4 | ANDROID | – | 91.99 |
| 5 | ROBOTICS | – | 94.99 |
-now question is retrieve the n max fees from course table .
– Select fees from (select fees ROW_NUMBER ( ) over ( partition by fees
Order by fees DESC ) as rownum from course ) where rownum = n + X.
– Select fees from (select fees RANK over ( partition by fees
Order by fees DESC ) as rank from course ) where rank = n + X .
– Select fees from (select fees DENSE_RANK () over ( partition by fees
Order by fees DESC ) as denserank from course ) where denserank = n .
X- ( NO OF DUPLICATES RECORDS – 1).
Example : for 3 max , X value will be ( 2 – 1) = 1 .
Rank variable value is ( 3 + ( 2 – 1) ) = 4 .
Row_number variable value is ( 3 + ( 2 – 1) ) = 4 .
- For above course table windows function values as follows :
| ID | NAME | DESCRIPTION | FEES | FEES(DESC) | RANK | ROW_NUMBER | DENSE_RANK |
| 1 | JAVA | – | 94.99 | 95.92 | 1 | 1 | 1 |
| 2 | .NET | – | 91.22 | 94.99 | 2 | 2 | 2 |
| 3 | SAP | – | 95.92 | 94.99 | 2 | 3 | 2 |
| 4 | ANDROID | – | 91.99 | 91.99 | 4 | 4 | 3 |
| 5 | ROBOTICS | – | 94.99 | 91.22 | 5 | 5 | 4 |
- From all 3 windows function DENSE_RANK is perfect one because for others 2 we should
Know the no of duplicates records regarding each column value , which is hard to know .
932 total views, 1 views today
Share this On
