SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpratyush
Starting Member

India
17 Posts

Posted - 11/20/2013 :  13:51:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 11/20/2013 :  14:03:36  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/21/2013 :  01:13:40  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000