how to work with Corelated queries

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

General SQL Server Forums

New to SQL Server Programming

how to work with Corelated queries

Author

Topic

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-02-04 : 07:24:34

hi all,
can plz any one let me to understand the fllow of following query

Use of corelated query To find the nth highest salary:-

Select Salary from Table1 T1 where N =
(Select Count(Distinct(Salary)) From Table1 T2 where
T2.Salary >= T1.Salary)

Where N is the desired no for which highest salary to be fetched.

where Table1 is

Name| Salary
asdf| 12345
qwe | 2351
sdf | 5684
gtr | 3210
mnnb| 321

I am clear of the funda that first outer query will be executed then Inner query ..
but how the comparison is going that is not clear to me plz if any one can help me to understand that.....

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 07:27:13

the inner query will give count of unique salaries which is greater than or equal to the salary passed by outer query.when this count is equated to N, we get salary for which there are N unique salaries existing which is equal to or greater than current one. This is in other words same as Nth highest salary.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:33:22

Are you using SQL Server 2000 or SQL Server 2005?

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

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-02-04 : 07:46:00

I am using sql server 2000. can this affect the result?

quote:
Originally posted by Peso

Are you using SQL Server 2000 or SQL Server 2005?

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

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 07:51:31

quote:
Originally posted by arorarahul.0688

hi all,
can plz any one let me to understand the fllow of following query

Use of corelated query To find the nth highest salary:-

Select Salary from Table1 T1 where N =
(Select Count(Distinct(Salary)) From Table1 T2 where
T2.Salary >= T1.Salary)

Where N is the desired no for which highest salary to be fetched.

where Table1 is

Name| Salary
asdf| 12345
qwe | 2351
sdf | 5684
gtr | 3210
mnnb| 321

I am clear of the funda that first outer query will be executed then Inner query ..
but how the comparison is going that is not clear to me plz if any one can help me to understand that.....

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

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

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-02-04 : 08:01:24

thanks for rply
bt i have doubt lets take another example
this is a table
a | roll
wr 2
aa 3
aa 4
bee 5
as 123
as 12
as 23
as 232

select * from testing t1 where 4 = ( select Count(Distinct(roll)) from testing t2 where t2.roll >= t1.roll)

results
as 12

according to corelated query mechanism inner query runs for each row given by outer query so how 12 can be 4th highest as in

pass 1
2 will be compared as 2>=2 results 1
2>=3 results 0
and so on

in pass2
3 will be compared as 3>=2 results 1
3>=3 results 1
3>=4 results 0
and so on
in pass3
5 will be compared as 4>=2 results 1
4>=3 results 1
4>=4 results 1
4>=5 results 0
and so on
in pass4
5 will be compared as 5>=2 results 1
5>=3 results 1
5>=4 results 1
5>=5 results 1
here no counts those are true for the give condition becomes 4 so 5 should be returned as result but as result i am getting 12
can u please tell me why ?

quote:
Originally posted by visakh16

the inner query will give count of unique salaries which is greater than or equal to the salary passed by outer query.when this count is equated to N, we get salary for which there are N unique salaries existing which is equal to or greater than current one. This is in other words same as Nth highest salary.

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 08:24:19

[code]For 'wr' and 2, get number of records that is 2 or more for Roll.
Result is 8 which means this is the 8th highest value.

For 'aa' and 3, get number of records that is 3 or more for Roll.
Result is 7 which means this is the 7th highest value.

For 'aa' and 4, get number of records that is 4 or more for Roll.
Result is 6 which means this is the 6th highest value.

For 'bee' and 5, get number of records that is 5 or more for Roll.
Result is 5 which means this is the 5th highest value.

For 'as' and 123, get number of records that is 123 or more for Roll.
Result is 2 which means this is the 2nd highest value.

For 'as' and 12, get number of records that is 12 or more for Roll.
Result is 4 which means this is the 4th highest value.

For 'as' and 23, get number of records that is 23 or more for Roll.
Result is 3 which means this is the 3rd highest value.

For 'as' and 232, get number of records that is 232 or more for Roll.
Result is 1 which means this is the 1st highest value.[/code]

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

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2008-02-04 : 08:33:05

thanks Peso thanks a lot
now i got it. thanks again.

quote:

Originally posted by Peso

For 'wr' and 2, get number of records that is 2 or more for Roll.
Result is 8 which means this is the 8th highest value.

For 'aa' and 3, get number of records that is 3 or more for Roll.
Result is 7 which means this is the 7th highest value.

For 'aa' and 4, get number of records that is 4 or more for Roll.
Result is 6 which means this is the 6th highest value.

For 'bee' and 5, get number of records that is 5 or more for Roll.
Result is 5 which means this is the 5th highest value.

For 'as' and 123, get number of records that is 123 or more for Roll.
Result is 2 which means this is the 2nd highest value.

For 'as' and 12, get number of records that is 12 or more for Roll.
Result is 4 which means this is the 4th highest value.

For 'as' and 23, get number of records that is 23 or more for Roll.
Result is 3 which means this is the 3rd highest value.

For 'as' and 232, get number of records that is 232 or more for Roll.
Result is 1 which means this is the 1st highest value.

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

Rahul Arora
07 Batch
NCCE Israna,

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 09:24:19

Also refer the 4th method described in the link I posted which is effecient

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources