Combining Top and Bottom Limits
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:
- 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)
As the employee’s manager, I may want to know the top three employees having the most vacation. I can use the LIMIT clause to find them:
dbadmin-> ORDER BY vac_days DESC LIMIT 3;
emp_name | vac_days
---------+----------
Peng | 1030
Jerry | 62
Charlie | 60
(3 rows)
On the flip side I may want to know the top three employee’s with the least amount of vacation remaining. To find these folks who are actually at the bottom overall, I can also use the LIMIT clause:
emp_name | vac_days
---------+----------
Bill | -5
Sally | 0
Mark | 8
(3 rows)
Note that the only difference in the queries to find the top three and the bottom three records is in the sort order.
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:
dbadmin-> ORDER BY vac_days DESC LIMIT 3
dbadmin-> UNION ALL
dbadmin-> SELECT * FROM emp_avail_vac_days
dbadmin-> ORDER BY vac_days LIMIT 3;
ERROR 4856: Syntax error at or near "ALL" at character 72
LINE 2: UNION ALL
. ^
Oops, that didn’t work!
I’ll need to isolate the queries into separate units of work by enclosing them in parenthesis:
dbadmin-> ORDER BY vac_days DESC LIMIT 3)
dbadmin-> UNION ALL
dbadmin-> (SELECT * FROM emp_avail_vac_days
dbadmin-> ORDER BY vac_days LIMIT 3);
emp_name | vac_days
---------+----------
Peng | 1030
Jerry | 62
Charlie | 60
Bill | -5
Sally | 0
Mark | 8
(6 rows)
That’s better! So, a final query for my report may look like this:
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);
Name | Days | 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)
Have fun!
No comments yet... Be the first to leave a reply!