w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.