Home » SQL & PL/SQL » SQL & PL/SQL » how it's working?
how it's working? [message #196621] Fri, 06 October 2006 04:44 Go to next message
vijayan_blr
Messages: 1
Registered: October 2006
Location: bangalore
Junior Member
Select a.marks from markTable a where (select count(*) from markTable b where b.marks>=a.marks)=3

this is the query for getting the Third highest value.


Can anybody explain how it's working?
Re: how it's working? [message #196625 is a reply to message #196621] Fri, 06 October 2006 05:05 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
this query will not satisfy you because it is showing wron value
the following is the query which will help you better.

--
select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
--

i hope this will work for you

regards
Muhammad shahid Mughal
Re: how it's working? [message #196627 is a reply to message #196621] Fri, 06 October 2006 05:07 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
for the lowest value of desired position
you should try this

select level, min('col_name') from my_table
where level = '&n'
connect by prior ('col_name') < 'col_name')
group by level;


-- Example:
--
-- Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second lowest salary:
--
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
--
this query is bonus for you

regards
Muhammad Shahid Mughal
Re: how it's working? [message #196667 is a reply to message #196627] Fri, 06 October 2006 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Useful queries, so thanks to @shahidmughal.

It doesn't quite answer the OP's question though.

What the query
Select a.marks from markTable a where (select count(*) from markTable b where b.marks>=a.marks)=3
does is this:

It steps through Marktable (a) , and for each row, it counts how mant rows there are in Marktable (b) with a mark greater than or equal to the mark from (a).
If this value is equal to 3, then it outputs the row from (a).
Re: how it's working? [message #196733 is a reply to message #196667] Fri, 06 October 2006 22:42 Go to previous message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

your query is working but not giving desired result by this way therefore i have uploaded that query to you.

your want to get 3rd highest value but it takes the value from 3 row and that value matched in where clause if you have 4 or 5 records of that value then it will show all.
but by this way it will show you 2nd highest value.

regards

Muhammad Shahid Mughal

Previous Topic: how can i create table
Next Topic: Help tune this SQL
Goto Forum:
  


Current Time: Sat Dec 03 20:00:28 CST 2016

Total time taken to generate the page: 0.18426 seconds