SQL Query - 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 2005 Forums

Transact-SQL (2005)

SQL Query

Author

Topic

shankar_ss
Starting Member

4 Posts

Posted - 2010-03-18 : 09:16:45

Hi All,

I am newbie to this forum. I need some details about below query. Assume I just tried to get second largest salary from a employee table. I tried below query which i got from google:

select sal from emp e
where 2 = (select count(*) from emp e1 where e.sal<=e1.sal)

I got the correct the second largest salary from above query. My doubt is how this query works. Specifically i would like to know how the clause "where 2 = (select count(*) from emp e1 where e.sal<=e1.sal)" works here.

Is the where clause used here is default sql syntax to get comparative results?.

Kindly explain me with few examples if possible.

Thanks,
Shankar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 09:26:14

First you should read it like this, that makes it a bit more clear (at least to me):
select sal from emp e
where (select count(*) from emp e1 where e.sal<=e1.sal) = 2

The logic is as follows
take the sal from the outer query and then in the subquery have a count on how many records are in there which have an equal or greater sal.
If the count is 2 then stop because you have the record that you want
Else go on with the next record in the outer query and do the count again
and so on..

No, you're never too old to Yak'n'Roll if you're too young to die.

shankar_ss
Starting Member

4 Posts

Posted - 2010-03-18 : 14:24:23

[/quote]
Thank you so much Webfred!! I am clear on this query. Thanks for explaining in more detailed way... Thanks a lot...

Regards,
Shankar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-19 : 02:38:11

Refer this
Points 5 and 6 are more better

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-19 : 04:28:56

quote:
Originally posted by shankar_ss

Thank you so much Webfred!! I am clear on this query. Thanks for explaining in more detailed way... Thanks a lot...

Regards,
Shankar

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources