2nd highest salary - 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)

2nd highest salary

Author

Topic

adnanans78
Starting Member

17 Posts

Posted - 2007-08-12 : 04:36:43

I am try to retrieve just 2nd highest salaries employees from the my emp table.Hope any one will help me..

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-12 : 09:22:45

Interview question? Homework?

SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary) AS d

E 12°55'05.25"
N 56°04'39.16"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-08-12 : 21:13:21

ORDER BY should be Salary DESC.
Needs DISTINCT in case of ties.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-13 : 02:17:32

Why do people always target the employee with second highest salary?

Madhivanan

Failing to plan is Planning to fail

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-08-13 : 03:11:24

The synax is

SELECT * FROM emp a WHERE n-1 = (select count(*) FROM emp b WHERE b.sal > a.sal)

Example for 2nd highest salary.
SELECT * FROM emp a WHERE 1 = (select count(*) FROM emp b WHERE b.sal > a.sal)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-13 : 12:48:59

SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC) AS d

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 13:29:50

You can nth highest salary from this query

here n = 2, you can create a procedure as well and pass value of n

SELECT salary
FROM TABLE T1
WHERE (n =
(SELECT COUNT(DISTINCT (T2.Salary))
FROM TABLE T2 WHERE T2.salary >= T1.salary))

Ashley Rhodes

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-13 : 14:24:05

since when did we start answering homework/interview questions here?

-ec

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 14:31:28

since forever

how does that matter what kind of questions they are

is there any rule that you cannot ask interview/homework questions in here.

Ashley Rhodes

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-13 : 14:52:15

Is the interviewer the guy with the 3rd highest salary?

Jim

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 15:02:01

how did you assume its interview question, it can be homework it might be the professor who has 3rd highest salary.

Ashley Rhodes

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-13 : 15:25:56

quote:
Originally posted by ashley.sql
is there any rule that you cannot ask interview/homework questions in here.

We shouldn't answer them. We should provide HELP or assistance to people, but we should not simply do their work for them. Especially if they are being evaluated somehow based on that work; i.e., an interview question or a homework question. People who are actually qualified should get the jobs and/or good grades, not people who just re-post questions to forums without making an effort at all to learn or do it themselves.

- Jeff
http://weblogs.sqlteam.com/JeffS

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-13 : 15:27:44

quote:
Originally posted by ashley.sql

since forever

how does that matter what kind of questions they are

is there any rule that you cannot ask interview/homework questions in here.

since forever? I guess forever extends to your 164 posts here.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources