Some tricky Queries in database | Techbirds
Posted on: December 6, 2013 /
Categories: Tutorials / Author Name: Ankit Shukla
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 ) ;
-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.
ROW_NUMBER assign unique no for each row starting with 1 .For rows having duplicate
values, numbers are arbitrary assigned .
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.
-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 :
- 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