The LIMIT clause is used to specify the maximum number of result set rows to return form a query.
Example:
Say we have a table EMP_AVAIL_VAC_DAYS that tracks the number of vacation days available for employees:
Code: Select all
dbadmin=> SELECT * FROM emp_avail_vac_days;
emp_name | vac_days
----------+----------
Patrick | 16
Charlie | 60
Brian | 22
Bill | -5
Jerry | 62
Jim | 14
Mark | 8
Peng | 1030
Sally | 0
Tom | 10
(10 rows)
Code: Select all
dbadmin=> SELECT * FROM emp_avail_vac_days ORDER BY vac_days DESC LIMIT 3;
emp_name | vac_days
----------+----------
Peng | 1030
Jerry | 62
Charlie | 60
(3 rows)
Code: Select all
dbadmin=> SELECT * FROM emp_avail_vac_days ORDER BY vac_days LIMIT 3;
emp_name | vac_days
----------+----------
Bill | -5
Sally | 0
Mark | 8
(3 rows)
Now I’d like a daily combined report showing me both the top three and bottom three employee’s in terms of the number of vacation days available. I should be able to simply combine the queries with the UNION ALL clause:
Code: Select all
dbadmin=> SELECT * FROM emp_avail_vac_days ORDER BY vac_days DESC LIMIT 3
dbadmin-> UNION ALL
dbadmin-> SELECT * FROM emp_avail_vac_days ORDER BY vac_days LIMIT 3;
ERROR 4856: Syntax error at or near "ALL" at character 72
LINE 2: UNION ALL
^
I’ll need to separate the queries into units of work by enclosing them in parenthesis:
Code: Select all
dbadmin=> (SELECT * FROM emp_avail_vac_days ORDER BY vac_days DESC LIMIT 3)
dbadmin-> UNION ALL
dbadmin-> (SELECT * FROM emp_avail_vac_days ORDER BY vac_days LIMIT 3);
emp_name | vac_days
----------+----------
Peng | 1030
Jerry | 62
Charlie | 60
Bill | -5
Sally | 0
Mark | 8
(6 rows)
Code: Select all
dbadmin=> (SELECT emp_name "Emp Name", vac_days "Vac Days Avail", 'TOP 3' "Rank"
dbadmin(> FROM emp_avail_vac_days
dbadmin(> ORDER BY vac_days DESC LIMIT 3)
dbadmin-> UNION ALL
dbadmin-> (SELECT emp_name, vac_days, 'BOTTOM 3'
dbadmin(> FROM emp_avail_vac_days
dbadmin(> ORDER BY vac_days LIMIT 3);
Emp Name | Vac Days Avail | Rank
----------+----------------+----------
Peng | 1030 | TOP 3
Jerry | 62 | TOP 3
Charlie | 60 | TOP 3
Bill | -5 | BOTTOM 3
Sally | 0 | BOTTOM 3
Mark | 8 | BOTTOM 3
(6 rows)