Home » SQL & PL/SQL » SQL & PL/SQL » Need to know how this query works
Need to know how this query works [message #217449] Fri, 02 February 2007 04:57 Go to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Hi,
just now i happen to see a query which finds the nth maximum salary from a given table. can any one explain me how actually this work.

Query is :

SELECT NAME,SALARY FROM EMP_MGR E
WHERE &SAM = (
SELECT COUNT(*) FROM EMP_MGR WHERE E.SALARY <= SALARY
);

where in &sam i will be the position of the rank.

Thanks in advance
sib
Re: Need to know how this query works [message #217503 is a reply to message #217449] Fri, 02 February 2007 10:03 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
for every row in the EMP_MGR table, find out how many other salaries are larger then the specific salary. &SAM is a replacement variable where you would enter (for example) 3. However this query will cause a fulll scan for every row and is inefficient.

[Updated on: Fri, 02 February 2007 10:03]

Report message to a moderator

Re: Need to know how this query works [message #217921 is a reply to message #217503] Tue, 06 February 2007 02:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could put an index on Salary, which would at least reduce it to an index range scan and a full table scan.
Previous Topic: How to use LIKE inside Where & When clauses?
Next Topic: where we will use hints in SQL ?and Why? plz tell me with the example ?
Goto Forum:
  


Current Time: Sat Dec 03 11:52:54 CST 2016

Total time taken to generate the page: 0.14223 seconds