Write top-n query output without writing a subquery

Moderator: NorbertKrupa

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

Write top-n query output without writing a subquery

Post by JimKnicely » Mon Apr 03, 2017 3:11 pm

How can we write top-n query output without writing a subquery?

Take this example:

SELECT T.ci,t.cv,T.val,T.updated_timestamp
FROM (
SELECT ci,cv,val,updated_timestamp,
RANK() OVER (PARTITION BY ci ORDER BY updated_timestamp desc) AS rank
FROM test_table
) as T
WHERE T.rank = 1 order by 1 ;


It can be re-written as:

SELECT ci,cv,val,updated_timestamp
FROM test_table
LIMIT 1 OVER(PARTITION BY ci ORDER BY updated_timestamp_desc);

Jim Knicely

Image

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

Post Reply

Return to “Vertica SQL”