Re: sql help please

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 31 Oct 2001 06:13:59 GMT
Message-ID: <HEMD7.11264$D5.2671413@typhoon.san.rr.com>

> i want the first row from each group having maximum count.

<snip>

Assuming you're using 8i or above ('*' = my additions) ...

select agdk, v, count from (
select substr(acct_group_dim_key,1,2) agdk,

      vehicle v, 
      count(grp_id) count,
*     first_value(vehicle) over (
*       partition by substr(acct_group_dim_key,1,2) 
*       order by count(grp_id) desc) as high_vehicle

from acct_group_dim
group by substr(acct_group_dim_key,1,2), vehicle order by agdk, count desc) tmp
* where v = high_vehicle

Note: this is non deterministic if there is a tie. Received on Wed Oct 31 2001 - 00:13:59 CST