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:

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:

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:

Oops, that didn’t work!

I’ll need to isolate the queries into separate units of work by enclosing them in parenthesis:

That’s better! So, a final query for my report may look like this:

Have fun!