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=> SELECT * FROM emp_avail_vac_days
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:

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)

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=> SELECT * FROM emp_avail_vac_days
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=> (SELECT * FROM emp_avail_vac_days
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=> (SELECT emp_name "Name", vac_days "Days", '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);
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!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.