Home » SQL & PL/SQL » SQL & PL/SQL » nth record from a sorted output of a table
nth record from a sorted output of a table [message #18468] Thu, 31 January 2002 22:59 Go to next message
Saji
Messages: 3
Registered: January 2002
Junior Member
How to get nth record from a sorted output of a table. I don't want the original nth position, but the position after sorting a table on a field.

Report message to a moderator

Re: nth record from a sorted output of a table [message #18473 is a reply to message #18468] Fri, 01 February 2002 00:38 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
From Oracle 8.1.6 or 8.1.7 on you got a new function which is very performant to do such things.

SELECT sal
FROM
( SELECT sal,
ROW_NUMBER() OVER (ORDER BY sal) s_rank
FROM emp
)
WHERE s_rank=4

Depending how you define the ranking you can also use RANK() or DENSE_RANK() instead of ROW_NUMBER() . The difference between RANK and DENSE_RANK is that DENSE_RANK leaves no gaps in ranking sequence when there are ties. That is, if you were ranking a competition using DENSE_RANK and had three people tie for second place, you would say that all three were in second place and that the next person came in third. The RANK function would also give three people in second place, but the next person would be in fifth place.

HTH
Mike

Report message to a moderator

Previous Topic: SQL Questions
Next Topic: how to write this select statement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 25 18:59:47 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.