All Forums Database
barani_sachin 141 posts Joined 01/12
24 Jul 2012
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.

 

ulrich 816 posts Joined 09/09
24 Jul 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.