PostgreSQL Subquery: Select last 10 records from a table

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

Sample Solution:

Code:

SELECT * FROM (
SELECT * FROM employees 
ORDER BY employee_id DESC LIMIT 10) sub 
ORDER BY employee_id ASC;

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.