Need Queries for Getting First Five Highest Salary Details

Expand Messages
• Hi, 1) I have a employee table where the details of empid, salary, deptid are stored. I want to return the first Five employees who are getting more salary.
Message 1 of 4 , Jul 31 8:59 PM
• 0 Attachment
Hi,

1) I have a employee table where the details of empid, salary, deptid are stored. I want to return the first Five employees who are getting more salary.
Could you please tell me the query?.

2) How to return the third highest salary from a table?.

Regards,
PremShankar

Send instant messages to your online friends http://uk.messenger.yahoo.com

[Non-text portions of this message have been removed]
• Hi Use the TOP n to get the answer to your first question. SELECT TOP 5 empid, salary FROM Employees ORDER BY salary DESC The answer to your second question is
Message 2 of 4 , Aug 1, 2006
• 0 Attachment
Hi
Use the TOP n to get the answer to your first question.
SELECT TOP 5 empid, salary FROM Employees ORDER BY salary DESC

The answer to your second question is a little trickier. What you need
to do here is rank the employees by salary. It gets tricky because you
will need to allow for ties, two to n employees with the same salary
amount. Here I am assuming you are using SQL Server 2000 or 2005. First
create a temp table -

SELECT empRank = IDENTITY(int), i.empid, i.salary
INTO #TempRank
FROM Employees e
WHERE 1=2

Then insert your employee info ordering by salary descending -

INSERT #TempRank(empid, salary)
FROM Employees
ORDER BY salary DESC

Now if you do a simple select where empRank = 3 you will get the FIRST
of the employees with a salary that is the third largest. To get ALL
employees with a salary that is third largest you need to do something
like this -

SELECT i.Ranking, r.empid, r.salary
FROM
(SELECT Ranking = MIN(t.empRank), t.salary FROM #TempRank t GROUP BY
t.salary) i, #TempRank r
WHERE r.salary = i.salary
ORDER BY i.Ranking

DROP TABLE #TempRank

This example is modeled on Ken Henderson's query on page 192 of his "The
Guru's Guide to Transact-SQL" book.
Hth,
Michael

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Rama Prem
Sent: Monday, July 31, 2006 8:59 PM
To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] Need Queries for Getting First Five Highest
Salary Details

Hi,

1) I have a employee table where the details of empid,
salary, deptid are stored. I want to return the first Five employees who
are getting more salary.
Could you please tell me the query?.

2) How to return the third highest salary from a table?.

Regards,
PremShankar

Send instant messages to your online friends
http://uk.messenger.yahoo.com

[Non-text portions of this message have been removed]

• Michael, Wouldn t it be that in your example you are retrieving the third row loaded into the table and NOT the third highest salary? It seems to me that it
Message 3 of 4 , Aug 1, 2006
• 0 Attachment
Michael,

Wouldn't it be that in your example you are retrieving the third row loaded
into the table and NOT the third highest salary?

It seems to me that it 'could' be that the third row just happens to have
the same salary value as the first row, or that the third row just happens
to have the same salary value as the second row.

(Aside: I much rather use table variables than #Temp tables, and definitely
prefer to explicitly create them rather than have them created as a result
of a Zero row insert.)

DECLARE @SalaryRanking table
( Rank int IDENTITY
, Salary Decimal
)

INSERT INTO @SalaryRanking
SELECT DISTINCT Salary FROM Employees
ORDER BY Salary DESC

Of course, at this point, to just retrieve the third highest Salary, one
only has to look in the @SalaryRanking table.

The following query will return all employees in the Employees table having
a Salary equal to or above the third highest Salary level.

SELECT
s.Rank
, e.empid
, e.salary
FROM @SalaryRanking s
JOIN Employees e
ON e.salary = s.salary
WHERE s.Rank <= 3
ORDER BY
s.Rank

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
Sent: Tuesday, August 01, 2006 9:51 AM

<snip>

Then insert your employee info ordering by salary descending -

INSERT #TempRank(empid, salary)
FROM Employees
ORDER BY salary DESC

Now if you do a simple select where empRank = 3 you will get the FIRST
of the employees with a salary that is the third largest. To get ALL
employees with a salary that is third largest you need to do something
like this -

Disclaimer - August 1, 2006
This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]
• Hmmm...could be unless I was loading the table with salary in descending order. As for temp tables vrs table variables, I would go with table variables. As for
Message 4 of 4 , Aug 1, 2006
• 0 Attachment
Hmmm...could be unless I was loading the table with salary in descending
order. As for temp tables vrs table variables, I would go with table
variables. As for creating temp tables explicitly rather than via a zero
row insert you are right that creating them explicitly is better.

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
Sent: Tuesday, August 01, 2006 1:24 PM
To: SQLQueriesNoCode@yahoogroups.com
Subject: RE: [SQLQueriesNoCode] Need Queries for Getting First Five
Highest Salary Details

Michael,

Wouldn't it be that in your example you are retrieving the third row
into the table and NOT the third highest salary?

It seems to me that it 'could' be that the third row just happens to
have
the same salary value as the first row, or that the third row just
happens
to have the same salary value as the second row.

(Aside: I much rather use table variables than #Temp tables, and
definitely
prefer to explicitly create them rather than have them created as a
result
of a Zero row insert.)

DECLARE @SalaryRanking table
( Rank int IDENTITY
, Salary Decimal
)

INSERT INTO @SalaryRanking
SELECT DISTINCT Salary FROM Employees
ORDER BY Salary DESC

Of course, at this point, to just retrieve the third highest Salary, one
only has to look in the @SalaryRanking table.

The following query will return all employees in the Employees table
having
a Salary equal to or above the third highest Salary level.

SELECT
s.Rank
, e.empid
, e.salary
FROM @SalaryRanking s
JOIN Employees e
ON e.salary = s.salary
WHERE s.Rank <= 3
ORDER BY
s.Rank

- Arnie Rowland

"I am a great believer in luck, and I find that the harder I work, the
more
I have of it." - Thomas Jefferson (1743-1826)

-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
Sent: Tuesday, August 01, 2006 9:51 AM

<snip>

Then insert your employee info ordering by salary descending -

INSERT #TempRank(empid, salary)
FROM Employees
ORDER BY salary DESC

Now if you do a simple select where empRank = 3 you will get the FIRST
of the employees with a salary that is the third largest. To get ALL
employees with a salary that is third largest you need to do something
like this -

Disclaimer - August 1, 2006
This email and any files transmitted with it are confidential and
intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the
named addressee you should not disseminate, distribute, copy or alter
this email. Any views or opinions presented in this email are solely
those of the author and might not represent those of Westwood
Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken
reasonable precautions to ensure no viruses are present in this email,
the company cannot accept responsibility for any loss or damage arising
from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

[Non-text portions of this message have been removed]