Can you pls provide more example of using Windows W Specfications

SQL
1

Hey Folks,

Am trying to replicate QUALIFY 1 windows ROW_NUMBER OVER PARTITION Clause using

Windows like the below example

QUALIFY 1 =ROW_NUMBER() OVER PARTITION BY MY COLUMN order by column

I want to achieve this in starburst but looks like Teradata Functions are not supported
As an alternative Window w is provided in Trino Documentation

Can you please review this and let me know what else needs to be changed to achieve TOP 1 Row

WINDOW w as (PARTITION BY column ORDER BY Column) - will this bring top 1 row
I see some Measure of Row between current row and current row as an example . But is not clear to me

2

Check out the last query in querying aviation data in the cloud (leveraging starburst galaxy) – Lester Martin (l11n) which shows you can use a CTE to do a ROW_NUMBER() OVER(PARTITION BY ...) and then just select from that temp table where the row number return is 1. Here’s an example from the TPCH connector IF I’m understanding your question correctly.

WITH ranked_orders_by_cust AS (
SELECT custkey, totalprice,
       ROW_NUMBER()
         OVER(PARTITION BY custkey
               ORDER BY totalprice DESC)
         AS ranking
  FROM  tpch.tiny.orders
)
SELECT custkey, totalprice
  FROM ranked_orders_by_cust
 WHERE ranking = 1
 ORDER BY custkey;
1 Like
3

Thanks for the example

1 Like