Find Nth Highest Salary in a table:
CREATE TABLE EMP
(
NAME VARCHAR(20),
SALARY INT,
DEPTNO INT
)
2nd Highest Salary in a table:
;WITH TMPTBL AS(SELECT TOP 2 NAME,SALARY FROM EMP ORDER BY SALARY DESC)
SELECT TOP 1* FROM TMPTBL ORDER BY SALARY
Or Using Row_Number()
;WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW = 2
Find Nth Highest Salary for each department in a table:
;WITH CTE AS(SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW=2
CREATE TABLE EMP
(
NAME VARCHAR(20),
SALARY INT,
DEPTNO INT
)
2nd Highest Salary in a table:
;WITH TMPTBL AS(SELECT TOP 2 NAME,SALARY FROM EMP ORDER BY SALARY DESC)
SELECT TOP 1* FROM TMPTBL ORDER BY SALARY
Or Using Row_Number()
;WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW = 2
Find Nth Highest Salary for each department in a table:
;WITH CTE AS(SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SALARY DESC) ROW,SALARY,DEPTNO FROM EMP)
SELECT * FROM CTE WHERE ROW=2
7 comments:
Grab the deal and buy the best fitness band under 2000 get the best fitness tracker now and best tracker in India. Shop the best fitness band under 2000 in india and measure your workout, heart rate, calories in one device. best fitness band in india under 2000
Purchase thoughtful gifts from best gifts store. Buy gifts and make your ordinary day even more special.Don't buy ordinary gifts buy unique gifts for your parents, unique gifts for your dad, unique gifts for men etc. gifts for sister
Grab some unknown gossip and know which are the upcoming web series coming on OTT platform.Want to know which are the web series going to release in India? Grab some
best and latest upcoming news in india. trending news in india
If you're an artist this is the opportunity for you to get freelance modelling jobs,
freelance acting jobs, professional freelance singers jobs, rapper and musicians
jobs as well. modeling jobs
Special moral and best motivational, attitude, sad, love, life, shayari, good night, moral story in hindi, good morning facebook, whatsapp status. motivational quotes hindi
Looking for the best fitness tracker in India? This is the place where you can get the best budget fitness band in India. best fitness band under 5000
Buy the best convection microwave oven in india with 100 % good quality buy now! best convection microwave oven
Post a Comment