queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-16 : 09:24:14

Mahavir writes "i want to get the Nth highest sal from emp table what will be the query

example i want 3rd highest sal
or 10 higetst sal or 50 highest sal"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-16 : 09:31:59

[CODE]
SELECT TOP 1 S.SALARY
FROM (SELECT TOP N SALARY
FROM SALS
ORDER BY SALARY )
AS S ORDER BY S.SALARY DESC
[/CODE]

where N = 5, 10 ... N for 5th, 10th .... Nth top salary

________________
Make love not war!

MegaTrain
Starting Member

16 Posts

Posted - 2003-12-16 : 09:59:50

Unless you'd like to pass it in as a parameter. In which case, that won't work.
Here's the entry from the FAQ titled "Dynamic SQL or How do I SELECT TOP @var records?"

http://sqlteam.com/item.asp?ItemID=233

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-16 : 10:33:54

Sure, if you want a variable Nth Salary you could go for something like :


DECLARE @TOP INT
DECLARE @SQL VARCHAR(1000)

SET @TOP = 3
SET @SQL = ''

SET @SQL = @SQL + 'SELECT TOP 1 S.SALARY AS ''Salary'' FROM (SELECT TOP ' + 
CAST(@TOP AS VARCHAR(4)) + ' SALARY  FROM SALS  ORDER BY SALARY )  AS S ORDER BY S.SALARY DESC'

EXEC (@SQL)

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources