Home » RDBMS Server » Server Administration » Re: simple query
Re: simple query [message #370383] Thu, 16 September 1999 09:48 Go to next message
m a sivan
Messages: 34
Registered: July 1999
Member
select distinct(sal) from emp where sal >=
(select sal from emp a where &n-1 =
(select count(distinct (sal)) from emp b where b.sal > a.sal)
and rownum and sal IS NOT NULL);

This gives for n top salaries where n is a runtime value...

Also were u satisfied with the previous QUery of
getting diff betn top two lengths...

U ccan refer to any boook on SQL but practise it hard and u will do good.
Re: simple query [message #370404 is a reply to message #370383] Tue, 21 September 1999 05:46 Go to previous messageGo to next message
deepak k
Messages: 3
Registered: September 1999
Junior Member
Thanks sivan,
thanks for ur reply, but i've not seen this type of queries before in any book which declares variable. i am not clear about this query's logic/processing can u explain it. & pl. give names of books where these kind of query's or the varible declaration part is covered. i need it badly.
thanking again.
also ur last ans was worked fine(wrt. diff betwn longest & 2nd longest ename string length .
deepak
Re: simple query [message #370407 is a reply to message #370404] Tue, 21 September 1999 09:28 Go to previous message
m a sivan
Messages: 34
Registered: July 1999
Member
Well I can explain the logic to you. Actually all the queries in ORCALE run from the right side. NOw the part in the braces is the subquery to which u are passing the table reference (a) of the base Query.

select sal from emp a where &n-1 =
(select count(distinct (sal)) from emp b where b.sal > a.sal)
and rownum and sal IS NOT NULL);

the part in the braces

select count(distinct (sal)) from emp b where b.sal > a.sal

just orders in the descending order ). and picks out the nth highest

and then the main part ( the most above part) displays all records which is less than that and distinct.

Well mey look a bit confusing if u are beginner but wont take much time if u do 1/2 complex queries like this daily.

There are no prescribed books for this but just basic books. As u know for everythings only basics are taught and expertise and mastery just comes by practise.
So just read basic books and try to read masters like Kevin Loney, Kaplan, thomas and also Bijus ORACLE home page. They are all wonderful writers and they know ORACLE inside out.

So browse some sites and soon u write better QUeries than these...
Previous Topic: Help me to Know About Rownum
Next Topic: Help Me simply Query
Goto Forum:
  


Current Time: Thu Oct 22 04:55:42 CDT 2020