CODEWITHSUNDEEP
sqloracle-database
Rupert Cobbe-Warburton
Rupert Cobbe-Warburton

Reputation: 653

Oracle - Selecting the n-1 record from a table

I have a table of data and want to retrieve the penultimate record.

How is this done?

TABLE:        results
              -------
              30
              31
              35

I need to get 31.

I've been trying with rownum but it doesn't seem to work.

Upvotes: 2

Views: 3654

Answers (2)

Narendra Mitta
Narendra Mitta

Reputation: 1

This can use for n th rank ##

select Total_amount from (select Total_amount, rank() over (order by Total_amount desc) Rank from tbl_booking)tbl_booking where Rank=3

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Assuming you want the second highest number and there are no ties

SELECT results
  FROM (SELECT results,
               rank() over (order by results desc) rnk
          FROM your_table_name)
 WHERE rnk = 2

Depending on how you want to handle ties, you may want either the rank, dense_rank, or row_number analytic function. If there are two 35's for example, would you want 35 returned? Or 31? If there are two 31's, would you want a single row returned? Or would you want both 31's returned.

Upvotes: 4

Related Questions