count distinct values suing olap approach

Database
Enthusiast

count distinct values suing olap approach

If I have a table with COL1, COL2, COL3 and COL4 with data as follows:

AA ZZ P 11 

AA ZZ Q 10

AA ZZ R 12

AA YY P 11

AA YY S 14

BB XX P 11

BB XX Q 10

For each distinct COL1 value I would like to find the COL3 value for the row with the highest COL4 value and would use

FIRST_VALUE(COL3) OVER (PARTITION BY COL1 ORDER BY COL 4 DESC)

but I would also like (in the same query) to count the number of distinct COL2 values, but I'm struggling to code this (without doing 2 passes of the data)

so for the above data my query should return

AA S 2

 BB P 1

My syntactically incorrect query attempt was

sel

COL1

,FIRST_VALUE(COL3) OVER (PARTITION BY COL1 ORDER BY COL 4 DESC)

,COUNT DISTINCT(COL2) OVER (PARTITION BY COL1)

FROM MYTABLE

QUALIFY --- I suspect I may need a qualify statement also

Any help would be much appreciated.

1 REPLY
Enthusiast

Re: count distinct values suing olap approach

You ONLY have to use OLAP approach? If not, you may try something like below.

Select A.Col1, A.Col3, B.Cnt

from (Sel Col1, col3, col4 from <Tbl>)A,

(Sel Col1, Count(distinct(Col2) cnt, Max(Col4) Mx

from <Tbl>

Group by 1)B

Where A.Col1=B.Col1

and A.Col4=B.Mx

Order by 1;

Thank you!

Ranga