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 |
|
hannm
Starting Member
6 Posts |
 Posted - 2011-03-29 : 22:19:40
|
how to find out 3rd maximum salary in the salary column?? astha |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-29 : 22:22:16
|
Hint : first use TOP 3 with ORDER BY and then find the MIN()
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
hannm
Starting Member
6 Posts |
Posted - 2011-03-29 : 22:30:11
|
can u help me writing the full query plz..?? cuz i havn't studied top command uptill now..don't know its syntax.. astha |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-29 : 22:48:18
|
sorry, we don't do homework or assignment here.You can refer here on how to use TOPhttp://www.w3schools.com/sql/sql_top.asphttp://www.quackit.com/sql/tutorial/sql_top.cfm
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
hannm
Starting Member
6 Posts |
Posted - 2011-03-29 : 23:02:59
|
| hey its not my homework...i was jst trying to solve more n more queries...n i tried it on my own bt m not able to do that its showing error 'group function not allowed' thats why i asked for help...anyways thanx 4 ur help. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-29 : 23:08:03
|
then post the query that you have tried and we will gladly help to explain or point out the problem
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
hannm
Starting Member
6 Posts |
Posted - 2011-03-30 : 06:49:14
|
| select top 3 salary from emp order by salary desc where sal=min(salary);astha |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-30 : 07:18:43
|
You are almost there. Your syntax is not quite right - the min function which is an aggregate function doesn't quite work the way you have written it - but your logic is trying to find the the top 3 salaries and pick the one with the lowest salary out of those three. One way to do that is to use a subquery like this:select top 1 salary from( select top 3 salary from emp order by salary desc) sorder by salary asc The inner query is finding the top 3, and the outer query is ordering them by salary in ascending order and picking the first one.Another way would be to use the min function in the outer query while keeping the inner query the same. Yet another way to do it is using the row_number() function which is available in SQL 2005 and higher.Oh! wait!! you were not asking for lecture, you were just asking how to solve the problem. I will shut up (for now :--) |
|
|
|
hannm
Starting Member
6 Posts |
Posted - 2011-03-30 : 07:58:23
|
| thnq so mch 4 answering as well as giving me d alternative methods to solve these types of queries...thnx yaastha |
|
|
|
|
|
|
sql query