2nd Last Record SQL in Oracle

2nd Last Record SQL in Oracle | In this post we will see how to find the 2nd last record SQL in Oracle database. For the demonstration and examples, we will use the EMP table. Oracle database contains EMP and DEPT tables under Scott user, but you can also create these tables with their data in any user. See:- EMP DEPT Tables Queries

Let us first display the table records row-wise, so that it can become easy to understand how to display 2nd last record SQL in Oracle database. To display records in row-wise row_number() function is very useful. 

Query to display empno, ename from EMP table, based on empno ascending order with row number.

SELECT empno, ename, row_number() OVER(
   ORDER BY empno ASC
) row_num
FROM emp;

Output:-

     EMPNO ENAME    ROW_NUM
---------- -------- --------
      7369 SMITH           1
      7499 ALLEN           2
      7521 WARD            3
      7566 JONES           4
      7654 MARTIN          5
      7698 BLAKE           6
      7782 CLARK           7
      7788 SCOTT           8
      7839 KING            9
      7844 TURNER          10
      7876 ADAMS           11
      7900 JAMES           12
      7902 FORD            13
      7934 MILLER          14

14 rows selected

Now from the above result, we want to display only the 2nd last record. To do this, we can use the WHERE clause. See the below query to get the 2nd last record for empno ascending order.

Example-1:- Query to display 2nd last record SQL in Oracle database based on empno column in emp table using row_number() function.

SELECT * FROM (
   SELECT empno, ename, row_number() OVER(
      ORDER BY empno DESC
   ) row_num
   FROM emp
) emp
WHERE row_num = 2;

Output:-

     EMPNO ENAME         ROW_NUM
---------- ---------- ----------
      7902 FORD                2

The empno = 7902 comes at 2nd last position i.e. 13th position when records are arranged in ascending order of empno column. But if we arrange the same record in descending order then it comes in the second position.

Let us see some other examples to get the second last record based on some other column values. The query will be very similar to the above SQL query, only we will arrange the records based on some other column.

Query to display empno, ename from EMP table, based on ename ascending order with row number.

SELECT empno, ename, row_number() OVER(
   ORDER BY ename ASC
) row_num
FROM emp;

Output:-

     EMPNO ENAME         ROW_NUM
---------- ---------- ----------
      7876 ADAMS               1
      7499 ALLEN               2
      7698 BLAKE               3
      7782 CLARK               4
      7902 FORD                5
      7900 JAMES               6
      7566 JONES               7
      7839 KING                8
      7654 MARTIN              9
      7934 MILLER             10
      7788 SCOTT              11
      7369 SMITH              12
      7844 TURNER             13
      7521 WARD               14

14 rows selected.

Example-2:- Query to display 2nd last record SQL in Oracle database based on ename column in emp table using row_number() function.

SELECT * FROM (
   SELECT empno, ename, row_number() OVER(
      ORDER BY ename DESC
   ) row_num
   FROM emp
) emp
WHERE row_num = 2;

Output:-

     EMPNO ENAME         ROW_NUM
---------- ---------- ----------
      7844 TURNER              2

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search