Need help with SQL Server query

Question

I want write this query in SQL Server

from (
    select DISTINCT salary 
    from employee 
    order by salary desc
) 
where rownum = 3;

So.. what is your question? Are gremlins stopping you from writing the query? — mellamokb
– mellamokb, Commented May 13, 2011 at 15:20

D'Arcy Rittich · Accepted Answer · 2011-05-13 15:27:07Z

7

See ROW_NUMBER():

E.g.,

WITH EmployeeSalary AS
(
    select salary, 
        ROW_NUMBER() OVER (order by salary desc) AS 'RowNumber'
    FROM employee 
    group by salary --you can't use DISTINCT because ROW_NUMBER() makes each row distinct
) 
SELECT * 
FROM EmployeeSalary 
WHERE RowNumber = 3;

edited May 13, 2011 at 15:27

answered May 13, 2011 at 15:20

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2011-05-13 15:24:03Z

0

SELECT DISTINCT salary 
FROM employee 
WHERE rownum = 3 
ORDER BY salary

The caps are optional. Is rownum a column in employee or are you looking for only the third row returned?

edited May 13, 2011 at 15:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 13, 2011 at 15:20

Brian Seim at EvoDynamic Inc

1581 gold badge2 silver badges10 bronze badges

2 Comments

marc_s Over a year ago

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

Avinash Narnaware Over a year ago

Thanks EvoD , rownum is not column. I m lokking for 3rd row

Collectives™ on Stack Overflow

Need help with SQL Server query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related