I have a requirement in that i have to use two qualify in the same query based on the value returned(this value is also returned by a qualify statement).
if the name is same then the order by should be age desc , rank desc
If the name is different then the order by should be rank desc, age asc.
Essentially i should have a two different qualify based on the name column.
can you share the SQL?
in general you can have a combined qualify condition
row_number() over (partition by key order by age desc, rank desc) = 1
min(name1) over (partiton by key) = max(name1) over (partiton by key)
) or (
row_number() over (partition by key order by age, rank) = 1
min(name1) over (partiton by key) <> max(name1) over (partiton by key)
the below SQL will give you the last day of an month in case the month has an odd number of day othewise the first date.
where year_of_calendar = 2012
count(*) over (partition by month_of_year) mod 2 = 0
and row_number() over (partition by month_of_year order by calendar_date) = 1
) or (
count(*) over (partition by month_of_year) mod 2 = 1
and row_number() over (partition by month_of_year order by calendar_date desc) = 1
order by calendar_date