Stored procedure - 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 2008 Forums

Transact-SQL (2008)

Stored procedure

Author

Topic

tpratyush
Starting Member

17 Posts

Posted - 2013-11-20 : 13:51:06

ID Name Salary Age Dob Address
1 Priyanka 45000 23 1990-09-27 Bbsr
2 Pratyush 67000 24 1990-04-03 Kolkata
3 Deepika 56000 23 1990-10-28 Kurda
4 Devika 89000 23 1989-12-15 Kolkata
5 Geetanjali 20989 24 1990-07-19 Bhutan
6 Satya 90000 23 1990-02-17 Nayagad
7 Titli 49000 24 1988-08-27 Kolkata

Write a Stored procedure in such a way that,
If I give an '6' as input parameter, the output will show the 6th highest salary.

Thanks and Regards,
Pratyush Tripathy

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 14:03:36

Order the data by SALARY DESC, and then take the sixth row out of that ordered collection. You can use ROW_NUMBER function to do this very efficiently. See here: http://technet.microsoft.com/en-us/library/ms186734.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 01:13:40

[code]
CREATE PROC GetNthSalary
@N int
AS
SELECT *
FROM
(
SELECT *,DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
FROM Table
)t
WHERE Rnk = @N
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources