How to fetch 6th highest record from employee table,columns are empid,ename,sal
empid column has primary key constraint.
asked
How to fetch 6th highest record from employee table?
everyone is guessing as to how to order the records, so can you clarify the '6th highest', in *what order*
If you want it by employee id (empid) then this should do what you need but you dont specify how the employee records should be ranked, you may have to change the column used.
SELECT MIN(empid)
FROM (
SELECT DISTINCT TOP(6)
empid
FROM employeetable
ORDER BY empid
) AS d
I'd use something like the below where you can change the ROW_NUMBER part to order or partition by whatever you want, dynamically even. I chose to order it by the empid, but you could just as easily do it by sal or name even.
SELECT et.empid, et.ename, et.sal
FROM dbo.employee_table et
INNER JOIN
(
SELECT empid
,ename
,sal
,ROW_NUMBER() OVER(ORDER BY empid asc) AS rownum
FROM dbo.employee_table
) empdrv
ON et.empid = empdrv.empid
WHERE empdrv.rownum = 6
BradyB - your solution works but I think it may suffer from being slower to return results as your inner select returns the whole table and the outer does the filter for the 6th place. this means the row_number has to be calculated for every row. You are also then using the rownum column to filter the record you want and that column isnt indexed. I haven't tried it on a big table but I think you may suffer problems when row counts get big. Its a good demonstration of how you can use Row_Number() though so thanks for posting it as an alternative.
(started this as a comment, but needed formatting...)
row_number() together with a CTE would be good - as long as it's SQL 2005+ obviously..
;with cte (empid,ename,sal,rownum) as
(
select empid,ename,sal,row_number()over (order by empid) as rownum
from employeetable
)
select empid,ename,sal from cte
where rownum = 6
Change the row_number() function to be over whatever column is needed to be sorted
Many more options and this will be a speed freak competition!!! Fighting the urge to start my virtual server here and test them out ... ! MUST . START . DECORATING .
Heh... man, I've seen this question asked a lot over the last 15 years. You'd think that folks would know how to use a search engine by now. I mean, what the heck, they can text while driving! ;-)
A small improvement on the first posted answer, to stop the search as soon as the row in 6th place is found:
DECLARE @Employee TABLE
(empid INT PRIMARY KEY,
ename NVARCHAR(50) NOT NULL,
sal MONEY NOT NULL UNIQUE (sal DESC, empid))
INSERT @Employee (empid, ename, sal)
VALUES (1, 'A', $10), (2, 'B', $20), (3, 'C', $30),
(4, 'D', $40), (5, 'E', $50), (6, 'F', $60),
(7, 'G', $70), (8, 'H', $80), (9, 'I', $90);
WITH SQ AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY sal DESC, empid ASC)
FROM @Employee E)
SELECT TOP (1)
SQ.empid, SQ.ename, SQ.sal
FROM SQ
WHERE SQ.rn = 6
ORDER BY SQ.rn;
You might want to use RANK / DENSE_RANK rather than ROW_NUMBER because there may not necessarily be a single row which is "6th highest". If there is more than one row tied for 6th ranking then the following returns all the rows:
SELECT EmployeeNumber, EmployeeName, Salary
FROM
(
SELECT EmployeeNumber
,EmployeeName
,Salary
,DENSE_RANK() OVER(ORDER BY Salary DESC) AS ROW_NUM
FROM dbo.Employee
) t
WHERE ROW_NUM = 6;
