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 |
|
jitendra
Starting Member
2 Posts |
 Posted - 2010-10-14 : 08:07:55
|
| I searched the query to get 2nd highest salary from a table & got the following query : " select e.salary from tbl_employee e where 1=(select count(*) from tbl_employee s where s.salary>e.salary)"i am new to Sql programming & hence not able to completely understand this query mainly the where clause. So, any help will be appreciated.Thanks in advance |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-14 : 08:56:39
|
The tbl_employee is read two times hence the aliases e and s.For each row in tbl_employee 'e' the subselect in the where clause is answering the question:"How many rows from tbl_employee 's' do have a higher salary than mine?"If the answer is 1 then the actual row must be the 2nd highest!Hope this is clear enough  because english isn't my native language.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-17 : 02:12:26
|
| If there are more than one person with the same salary which is the highest then this wont work.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-17 : 02:13:59
|
in that case you may do like this select e.salary from tbl_employee ewhere 1=(select count(distinct salary) from tbl_employee swhere s.salary>e.salary)" ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jitendra
Starting Member
2 Posts |
Posted - 2010-10-20 : 01:10:13
|
| Thanks webfred...it is clear to me & though english isn't your native language but you explained it nicely. Also Thanks to visakh16 for providing the help. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-20 : 02:21:50
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Select Nth highest salary