PostgreSQL Subquery: Select last 10 records from a table

17. Write a subquery to select last 10 records from a table.

Sample Solution:

Code:

-- This SQL query selects the last 10 records from the employees table based on employee_id in descending order, and then reorders them in ascending order based on employee_id.

SELECT * -- Selects all columns from the result set
FROM ( -- Subquery: Selects the last 10 records from the employees table in descending order of employee_id
    SELECT * -- Selects all columns from the employees table
    FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
    ORDER BY employee_id DESC -- Orders the result set by employee_id in descending order
    LIMIT 10 -- Limits the result set to the first 10 records
) sub -- Aliases the result set as 'sub'
ORDER BY employee_id ASC; -- Reorders the result set from the subquery by employee_id in ascending order

Explanation:

• This SQL query selects the last 10 records from the employees table based on employee_id in descending order and then reorders them in ascending order based on employee_id.
• The outermost SELECT statement retrieves all columns from the result set.
• The subquery selects all columns from the employees table, orders the result set by employee_id in descending order, and limits the result set to the first 10 records.
• The subquery result set is aliased as 'sub'.
• The outermost SELECT statement then reorders the result set from the subquery by employee_id in ascending order.

Sample table: employees

Output:

pg_exercises=# SELECT * FROM (
pg_exercises(# SELECT * FROM employees
pg_exercises(# ORDER BY employee_id DESC LIMIT 10) sub
pg_exercises-# ORDER BY employee_id ASC;
 employee_id | first_name | last_name |     email     | phone_number | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
         197 | Kevin      | Feeney    | not available | 650.507.9822 | 1987-09-22 | SH_CLERK   |  3000.00 |           0.00 |        124 |            50
         198 | Donald     | OConnell  | not available | 650.507.9833 | 1987-09-23 | SH_CLERK   |  2600.00 |           0.00 |        124 |            50
         199 | Douglas    | Grant     | not available | 650.507.9844 | 1987-09-24 | SH_CLERK   |  2600.00 |           0.00 |        124 |            50
         200 | Jennifer   | Whalen    | not available | 515.123.4444 | 1987-09-25 | AD_ASST    |  4400.00 |           0.00 |        101 |            10
         201 | Michael    | Hartstein | not available | 515.123.5555 | 1987-09-26 | MK_MAN     | 13000.00 |           0.00 |        100 |            20
         202 | Pat        | Fay       | not available | 603.123.6666 | 1987-09-27 | MK_REP     |  6000.00 |           0.00 |        201 |            20
         203 | Susan      | Mavris    | not available | 515.123.7777 | 1987-09-28 | HR_REP     |  6500.00 |           0.00 |        101 |            40
         204 | Hermann    | Baer      | not available | 515.123.8888 | 1987-09-29 | PR_REP     | 10000.00 |           0.00 |        101 |            70
         205 | Shelley    | Higgins   | not available | 515.123.8080 | 1987-09-30 | AC_MGR     | 12000.00 |           0.00 |        101 |           110
         206 | William    | Gietz     | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110
(10 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a subquery to find the 4th minimum salary of all the salaries.
Next: Write a subquery to display the information for all the departments where no employee is working.