question

ashok kumar avatar image
ashok kumar asked

How to fetch 6th highest record from employee table?

How to fetch 6th highest record from employee table,columns are empid,ename,sal

empid column has primary key constraint.

t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

everyone is guessing as to how to order the records, so can you clarify the '6th highest', in *what order*
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Only 1 to go!!!
0 Likes 0 ·
Ah, the good old sympathy vote, thanks Jonlee ;)
0 Likes 0 ·
BradyB avatar image
BradyB answered

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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

(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

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 .
0 Likes 0 ·
This is the method I would have suggested.
0 Likes 0 ·
+1, me too, exactly as I would have done it
0 Likes 0 ·
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! ;-)
0 Likes 0 ·
SQL Kiwi avatar image
SQL Kiwi answered

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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.