Write top-n query output without writing a subquery
Posted: 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);
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);