Home » SQL & PL/SQL » SQL & PL/SQL » please explain this statement
please explain this statement [message #299674] Tue, 12 February 2008 23:20 Go to next message
raghuram.sundar
Messages: 11
Registered: February 2008
Location: CHENNAI,INDIA
Junior Member

hi all,

PLease explaing how this statement works

select sal from emp a
where (select count(distinct sal) from emp where a.sal<=sal)=4

This code brings me the 4 th max salry in the column

it is obvious that the statement select count(distinct sal) from emp where a.sal<=sal will return 4 .

so if we say
select sal from emp a
where 4=4 it returns no rows PLease explain if someone knows about this.
Re: please explain this statement [message #299707 is a reply to message #299674] Wed, 13 February 2008 00:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
>it is obvious that the statement select count(distinct sal) from emp where a.sal<=sal will return 4 .

False. If put standalone, it will end with error, as it is correlated to the outer query through A.SAL column.
In the whole query, it returns different results for different rows in the outer query depending on their SAL value. For some rows (you want) it returns 4.

However, this approach is not efficient for large tables; when the table has N rows, it is scannes N+1 times.
Rather use Oracle analytics, have a look at DENSE_RANK function.
Re: please explain this statement [message #299718 is a reply to message #299707] Wed, 13 February 2008 00:29 Go to previous messageGo to next message
raghuram.sundar
Messages: 11
Registered: February 2008
Location: CHENNAI,INDIA
Junior Member

Thanks For your adivce

Using DENSE_RANK function is it possible to retreive only 3 rd ranked column. Please provide me the link regarding the same
Re: please explain this statement [message #299721 is a reply to message #299674] Wed, 13 February 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is obvious that the statement select count(distinct sal) from emp where a.sal<=sal will return 4

SQL> select a.sal, (select count(distinct sal) from emp where a.sal<=sal) nbsal
  2  from emp a;
       SAL      NBSAL
---------- ----------
       800         12
      1600          6
      1250          9
      2975          3
      1250          9
      2850          4
      2450          5
      3000          2
      5000          1
      1500          7
      1100         10
       950         11
      3000          2
      1300          8

14 rows selected.

Not so obvious.

Quote:
if we say select sal from emp a where 4=4 it returns no rows

I should think it will return all rows.

DENSE_RANK as well as other ranking functions are in the documentation, don't you know how to find it? Here's a link SQL Reference

Regards
Michel

Re: please explain this statement [message #299723 is a reply to message #299674] Wed, 13 February 2008 00:41 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Using DENSE_RANK function is it possible to retreive only 3 rd ranked column.

Is it statement or question? If I substitute column (which does not make sense to me) with row, the answer would be yes.

> Please provide me the link regarding the same

In OraFAQ Forum Guide in Useful Sites tab, there is a link to The Online documentation. A good start when searching anything, eg. DENSE_RANK

[Edit: extended the first answer]

[Updated on: Wed, 13 February 2008 00:44]

Report message to a moderator

Re: please explain this statement [message #299777 is a reply to message #299723] Wed, 13 February 2008 03:07 Go to previous messageGo to next message
raghuram.sundar
Messages: 11
Registered: February 2008
Location: CHENNAI,INDIA
Junior Member

can anyone differentiate left outer and right outer join

and full outer join for me

using this below code??

SELECT * FROM T_SH_1 S1 FULL OUTER JOIN T_SH_2 S2 ON(S1.PATIENTID = S2.PATIENTID)
FULL OUTER JOIN T_SH_3 S3 ON(S2.PATIENTID = S3.PATIENTID)
JOIN V_KEY K ON(S1.PATIENTID = K.PATIENTID OR S2.PATIENTID = K.PATIENTID OR S3.PATIENTID = K.PATIENTID)
JOIN V_KEY_VIS KV ON(KV.ID=S1.VISITID OR KV.ID=S2.VISITID OR KV.ID=S3.VISITID);
Re: please explain this statement [message #299780 is a reply to message #299777] Wed, 13 February 2008 03:11 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Joins

Regards
Michel
Previous Topic: Query giving wrong result on time
Next Topic: Numbers from 1 to 10
Goto Forum:
  


Current Time: Sun Dec 11 04:30:29 CST 2016

Total time taken to generate the page: 0.04779 seconds