If sarlary is in your table, you could use TOP 5 to find out highest salary from your table. There is a sample query below.
declare @table table(id int identity(1,1), dept varchar(20), values2 varchar(20),salary int)
insert into @table
select 'dept-1','values2-1',1000 union all
select 'dept-2','values2-2',2000 union all
select 'dept-3','values2-3',3000 union all
select 'dept-4','values2-4',4000 union all
select 'dept-1','values2-1',4500 union all
select 'dept-2','values2-2' ,6000
select top 5 * from @table
order by salary desc
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
spapim is right. but if you want 5th highest drawn by employees you may try this
with CTE as
(
select t.*, ROW_NUMBER() OVER( partition by employee_id ORDER BY t.Salary desc) AS RowNum
from MyTable as t
)
select * from CTE
where RowNum = 5
You will get one row for each employee with 5th highest salary he drawn till now.
you can also get department wise 5th highest salary as below
with CTE as
(
select t.*, ROW_NUMBER() OVER( partition by department_id ORDER BY t.Salary desc) AS RowNum
from MyTable as t
)
select * from CTE
where RowNum = 5
single row for each department with 5th highest salary of that department
feel free to ask if you need anything else.
Shuvo
If more posts give you useful answers, Please mark each post as "Answer".
How to find 5th highest salary from table without using subquery ?
This can also be achieved using RANK() function:
declare @test table (employeeid int, salary numeric(20, 4))
insert into @test(employeeid, salary)
select 1001, 127312
union all
select 1002, 354454
union all
select 1005, 87454534
union all
select 1007, 2124254
union all
select 1008, 54544453
union all
select 1009, 12763781
union all
select 1010, 553411
union all
select 1011, 844455
;WITH a AS
(
SELECT RANK() OVER(ORDER BY salary DESC) 'Rank'
, salary
, employeeid
FROM @test
)
select * from a where [RANK] = 5;
Regards,
Yoga
Marked as answer by Chen Yu - MSFT on Jul 16, 2012 02:42 AM
rakesh.sawan...
Member
168 Points
173 Posts
How to find 5th highest salary from table without using subquery ?
Jul 04, 2012 05:09 PM|LINK
How to find 5th highest salary from table without using subquery ?
spapim
Contributor
2686 Points
393 Posts
Re: How to find 5th highest salary from table without using subquery ?
Jul 04, 2012 05:25 PM|LINK
Try something like that:
with CTE as ( select t.*, ROW_NUMBER() OVER(ORDER BY t.Salary desc) AS RowNum from MyTable as t ) select * from CTE where RowNum = 5Hope this helps.
www.imobiliariasemsuzano.com.br
wmec
Contributor
6556 Points
3325 Posts
Re: How to find 5th highest salary from table without using subquery ?
Jul 05, 2012 01:35 AM|LINK
With source1
As
(
select tab1.*,Row_Num1ber() Over(Order By tab1.salary desc) As Row_Num1
From tab1
)
Select *
From source1
Where Row_Num1 = 5;
HuaMin Chen
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: How to find 5th highest salary from table without using subquery ?
Jul 10, 2012 05:54 AM|LINK
Hi,
If sarlary is in your table, you could use TOP 5 to find out highest salary from your table. There is a sample query below.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store
Shuvo Aymon
Contributor
4820 Points
1167 Posts
Re: How to find 5th highest salary from table without using subquery ?
Jul 10, 2012 06:06 AM|LINK
spapim is right. but if you want 5th highest drawn by employees you may try this
with CTE as ( select t.*, ROW_NUMBER() OVER( partition by employee_id ORDER BY t.Salary desc) AS RowNum from MyTable as t ) select * from CTE where RowNum = 5You will get one row for each employee with 5th highest salary he drawn till now.
you can also get department wise 5th highest salary as below
with CTE as ( select t.*, ROW_NUMBER() OVER( partition by department_id ORDER BY t.Salary desc) AS RowNum from MyTable as t ) select * from CTE where RowNum = 5single row for each department with 5th highest salary of that department
feel free to ask if you need anything else.
If more posts give you useful answers, Please mark each post as "Answer".
sandeepmitta...
Contributor
6959 Points
1084 Posts
Re: How to find 5th highest salary from table without using subquery ?
Jul 10, 2012 06:12 AM|LINK
Refer this post. There are various ways in the post to find the same.
http://itdeveloperzone.blogspot.in/2012/01/find-nth-highest-salary-in-sql.html
Sandeep Mittal | My Blog - IT Developer Zone
tgyoga
Contributor
2378 Points
393 Posts
Re: How to find 5th highest salary from table without using subquery ?
Jul 10, 2012 06:56 AM|LINK
This can also be achieved using RANK() function:
Yoga