select 2nd row in Plsql

Question

Lets say I have the following table:

 SomeTable(
    id, 
    price
 )

How do I select the 2nd highest priced row from this table? Note : This has to be done in Pl/SQL, in a database agnostic way. Is it possible to do this without any loops?

I know how this is done using Oracle constructs like rownum or mysql constructs like limit, so I am not looking for those.

how do you expect a solution based on an oracle proprietary pl/sql to be database-agnostic? — Kirill Leontev, Dec 04 '12 at 13:08
I didnt mean Oracle proprietary pl-sql. I meant a code block - which would probably run anywhere - lets say Java for the sake of commenting — Achow, Dec 05 '12 at 04:10

score 1 · Answer 1 · answered Dec 04 '12 at 10:12

1

CREATE TABLE mytable (id NUMBER PRIMARY KEY, price NUMBER NOT NULL);
INSERT INTO mytable VALUES (1, 10);
INSERT INTO mytable VALUES (2, 20);
INSERT INTO mytable VALUES (3, 20);
INSERT INTO mytable VALUES (4, 30);

SELECT id, price 
 FROM (
       SELECT id, price, RANK() OVER (ORDER BY price DESC) AS r
         FROM mytable
      )
 WHERE r=2;

 ID PRICE
--- -----
  2    20
  3    20

answered Dec 04 '12 at 10:12

wolφi

8,091
2
35
64

I thought RANK() was an analytic function used in Oracle.I wanted a code block,preferably. – Achow Dec 05 '12 at 04:11
1

Sorry, I misunderstood your question. In that case, I'd go for `SELECT * FROM SomeTable ORDER BY price DESC`, discard the first row and fetch another one. That should be as database agnostic as it gets... – wolφi Dec 05 '12 at 14:29

score 1 · Accepted Answer · answered Jan 31 '13 at 09:29

1

Isn't this simple? God knows why I didn't think about it before!

select max(price) from tnum where price <> (select max(price) from tnum)

answered Jan 31 '13 at 09:29

Achow

8,600
6
39
49

select 2nd row in Plsql

2 Answers2

Linked

Related