Tuesday, August 12, 2008

Oracle SQL get second last row record

SQL> CREATE TABLE
EMP(EMPID VARCHAR2(3),
SALARY NUMBER(8,2));

Table created.

SQL> INSERT INTO EMP
VALUES ('&A',&B);
Enter value for a: 1
Enter value for b: 70000
old 1: INSERT INTO EMP VALUES ('&A',&B)
new 1: INSERT INTO EMP VALUES ('1',70000)

1 row created.

SQL> /
Enter value for a: 2
Enter value for b: 80000
old 1: INSERT INTO EMP VALUES ('&A',&B)
new 1: INSERT INTO EMP VALUES ('2',80000)
1 row created.

SQL> /
Enter value for a: 5
Enter value for b: 56765
old 1: INSERT INTO EMP VALUES ('&A',&B)
new 1: INSERT INTO EMP VALUES ('5',56765)
1 row created.

SQL> /
Enter value for a: 4
Enter value for b: 42343
old 1: INSERT INTO EMP VALUES ('&A',&B)
new 1: INSERT INTO EMP VALUES ('4',42343)1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMP SALARY
--- ----------
1 70000
2 80000
5 56765
4 42343

[b]
(SELECT * FROM (SELECT (ROWNUM) RN, EMPID,SALARY FROM EMP)
WHERE RN >( SELECT MAX(ROWNUM)-2 FROM EMP))
MINUS
(SELECT * FROM (SELECT (ROWNUM) RN,EMPID,SALARY FROM EMP)
WHERE RN >( SELECT MAX(ROWNUM)-1 FROM EMP));

[/b]

RN EMP SALARY
---------- --- ----------
3 5 56765

No comments: