Was also going to say the Qualify is a nice addition as you can Filter the Analytical functions in the same query. Like HAVING for analytical functions. But you have to have a where clause e.g. SELECT x,y FROM tab1 WHERE TRUE QUALIFY ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC)=1
This is really good stuff! I wasn’t aware of it. Just had a look, it seems pretty new still in preview. Hope it goes into GA soon. Indeed a very powerful addition!
Nice. I’m always adding my row_number to a qualify ….=1 instead of adding it to a column first and then filtering out. Are there any pros/cons with that?
Hi Christian, can you give an example, not sure I understand the syntax you are referring to? If it’s the latest version you want to extract out, filter has to be done after you computed the row number, in other words it needs to be done in a outer query or make the select a CTE (common table expression for any readers don’t know this term) then filter. Is there a way to do the filtering without doing it in two steps?
