Combining Top and Bottom Limits

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Combining Top and Bottom Limits

Post by JimKnicely » Mon Apr 15, 2013 6:07 pm

Hi,

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)
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:

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)
On the flip side I may want to know the top three employee’s with the least amount of vacation remaining. For this information I can also use the LIMIT clause:

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)
Note that the only difference in the two queries above 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:

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
               ^
Oops, that didn’t work!

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)
That’s better! So, a final query for my report may look like this:

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)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”