Home » SQL & PL/SQL » SQL & PL/SQL » Display nth row from a table
icon6.gif  Display nth row from a table [message #290512] Sat, 29 December 2007 05:29 Go to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
hello members,
this is a nice forum and this is my first post. I would like to know how to display the nth row from a table. I saw a few threads but they answer : 'by using rownum'

however, i don't want to see 1 to n but only the nth row, eg. the 5th highest salary from employees table.

I have worked out a code... kindly advice.

Quote:

SELECT rank, salary
from (select rownum as rank, salary
from (select distinct salary
from employees
order by salary desc)
)
where rank = 5;


Now if u feel that this query is right.... i wish to ask an extension to it...

Here I have taken 'distinct' and hence, all duplicate values are not shown. Now i want to show all the salaries which are equal to 5th highest salary i.e there are 2 more employees who earn that much.

So how do i do that?
Re: Display nth row from a table [message #290514 is a reply to message #290512] Sat, 29 December 2007 05:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
search for analytical functions in sql reference manual.

Regards

Raj
Re: Display nth row from a table [message #290515 is a reply to message #290512] Sat, 29 December 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DENSE_RANK

Regards
Michel
Re: Display nth row from a table [message #290518 is a reply to message #290515] Sat, 29 December 2007 06:18 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
dear sir(s)
the link given was for oracle 10g but i am using 9i. Kindly give the soluton, pertaining to 9i
Re: Display nth row from a table [message #290519 is a reply to message #290518] Sat, 29 December 2007 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DENSE_RANK function existed since 8.1.6.
If you just tried it or searched in your documentation you'd find it.
Are you too lazy for this?

By the way, 9.0.2 is a version that never existed.

Regards
Michel

[Updated on: Sat, 29 December 2007 06:34]

Report message to a moderator

icon10.gif  Re: Display nth row from a table [message #290634 is a reply to message #290512] Sun, 30 December 2007 23:13 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,
you can select the following three ways

select salary from(select rownum rank, salary
from ( select salary from emp order by salary desc))
where rank=&N

SELECT empno,ename,job,mgr,hiredate,sal FROM (SELECT empno,ename,job,mgr,hiredate,sal,dense_rank() OVER (ORDER BY sal DESC) Topn FROM emp)WHERE Topn=3


SELECT * FROM emp a WHERE &N = (SELECT count(DISTINCT
(b.sal)) FROM emp b WHERE a.sal<=b.sal);

Thanks
Mano
Re: Display nth row from a table [message #290639 is a reply to message #290634] Mon, 31 December 2007 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, once again, read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Display nth row from a table [message #290675 is a reply to message #290512] Mon, 31 December 2007 01:42 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

In this code, i get the nth max results by salary field. You can substitute salary by your own field.

SELECT * FROM Table_Name a
WHERE n = (SELECT COUNT(salary)
FROM Table_Name b
WHERE a.salary >= b.salary);

Regards,
Suresh.
Re: Display nth row from a table [message #290722 is a reply to message #290675] Mon, 31 December 2007 04:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Suresh,

I completely agree your code will achieve the desired results. But this is a very old technique of retrieving nth record, nth maximum <column> which is quite often asked in the interviews. But performance wise these kind of queries are killers. If you search in the oracle reference manual for "analytical functions" you can achieve the same and it much more efficient and faster and uses less cpu, consistent reads. Do a tkprof between the queries and you will understand the huge performance gain you will get by using analytical functions.

No Offense just thought of letting you know.

Regards

Raj

P.S : Re-worded

[Updated on: Mon, 31 December 2007 04:41]

Report message to a moderator

Re: Display nth row from a table [message #290732 is a reply to message #290512] Mon, 31 December 2007 05:23 Go to previous messageGo to next message
cgmsuresh
Messages: 29
Registered: December 2007
Location: http://maps.google.com/ma...
Junior Member

Rajaram,

Please let me know the link for analytical functions. I have Oracle 10g Reference Manual. I searched for it. But i can't get the optimized queries. I am very much thankful to you, if you provide me the correct link OR give me the correct(optimized) queries.

Regards,
Suresh.
Re: Display nth row from a table [message #290735 is a reply to message #290732] Mon, 31 December 2007 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See above:
Michel Cadot wrote on Sat, 29 December 2007 12:49

DENSE_RANK

Regards
Michel


Re: Display nth row from a table [message #290747 is a reply to message #290732] Mon, 31 December 2007 07:06 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Till date i have been calling it as Analytical Functions. But apparently it is called Analytic Functions. Apologies for that. You could have googled it, anyways thankfully Michael has given you the link for one of the Analytic Functions. As I mentioned earlier, search in the oracle sql reference manual for "Analytic functions" it will give you the list of all the analytic functions available and is been explained in a detailed way about the syntax and how it works. As Tom (thomas kyte) rightly said, after "select statement" analytic functions is an excellent piece of tool available for oracle programmers. he is 100% correct. It is simply great and the beauty is, Oracle allows you to write your own Analytic functions. So incase if you can't find an analytic functions which suits your requirement you can write your own. Ofcourse it may not be as efficient as the inbuilt but still it is interesting.

Happy learning Analytic functions. It is simply a piece of art.

Regards

Raj
Previous Topic: adding constraints
Next Topic: ANYDATA
Goto Forum:
  


Current Time: Fri Dec 02 12:15:13 CST 2016

Total time taken to generate the page: 0.09628 seconds