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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-18 : 08:40:11
|
| Prashant writes "Table : Cust_MstrFields : CustNo, Name, SalaryThere are five records (1) C0001, Prashant, 5000(2) C0002, Ravi, 15000(3) C0003, Ami, 2000(4) C0004, Maulik, 20000(5) C0005, Amit, 18000Now i want to find the second highest salary.Please Help me out as early as possible." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-18 : 09:06:20
|
| select top 1 * from (select top 2 * from tbl order by salary desc) order by salary==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-01-18 : 23:43:22
|
| I don't think that will work when there are many people with the highest salary. You'd need something likeselect max(salary ) from tbl where salary <(select max(salary ) from tbl )which gets your salary. Then if you wanted those earning that salary you'd have to put into a where clause :select * from tbl where salary=(select max(salary ) from tbl where salary <(select max(salary ) from tbl )) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-19 : 01:10:50
|
| Nr's query gets the second highest salary. LoztInSpaces query gets the second highest unique salary.Without more information from OP, both queries are valid.Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Finding Second Highest Salary