How to fetch 6th highest record from employee table,columns are empid,ename,sal
empid column has primary key constraint.
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
(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
row_number() function to be over whatever column is needed to be sorted
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;
No one has followed this question yet.