TOP WITH TIES in Vertica?

[debfawn]

SQL Server has a TOP n WITH TIES command that will return the greatest n values and includes ties (those that are equal).

In Vertica we have the LIMIT command and I can get the TOP 3 values using a LIMIT 3 in combination with ordering the data descending...

Code: Select all

dbadmin=> select * from top;
 numbers
---------
       5
       5
       1
       2
       3
       4
(6 rows)

dbadmin=> select * from top order by numbers desc limit 3;
 numbers
---------
       5
       5
       4
(3 rows)

But want I want is 5, 5, 4 and 3. Using the WITH TIES option in SQL Server can get me this.

How can I do this in Vertica?

[JimKnicely]

Here is one way:

Code: Select all

dbadmin=> SELECT numbers FROM top WHERE numbers IN (SELECT DISTINCT numbers FROM top ORDER BY numbers DESC LIMIT 3) ORDER BY numbers DESC;
 numbers
---------
       5
       5
       4
       3
(4 rows)

But I wouldn't expect great performance out of that query on a large data set

[scutter]

Use the DENSE_RANK() analystic function?

select numbers from (
select numbers, dense_rank() over (order by numbers desc) as rk from top
) q
where rk <= 3
order by rk;

5
5
4
3

[JimKnicely]

Nice scutter! A much better solution!

[debfawn]

Awesome as usual! Thanks!!!