is it possible to use two qualify in a query based on some condition?

Database
Enthusiast

is it possible to use two qualify in a query based on some condition?

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).

Ex :

 Ex1




NAME1 NAME2 AGE RANK
A B 1 1
A C 2 2

if the name is same then the order by should be age desc , rank desc

Ex2




NAME1 NAME2 AGE RANK
X B 1 1
Y C 2 2

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.

Tags (3)
1 REPLY
Supporter

Re: is it possible to use two qualify in a query based on some condition?

can you share the SQL?

in general you can have a combined qualify condition

like 

qualify (
row_number() over (partition by key order by age desc, rank desc) = 1
and
min(name1) over (partiton by key) = max(name1) over (partiton by key)
) or (
row_number() over (partition by key order by age, rank) = 1
and
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.

select *
from sys_calendar.calendar
where year_of_calendar = 2012
qualify (
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