Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql help please
Thank you very much
Richard Kuhler <noone_at_nowhere.com> wrote in message
news:ZEMD7.11265$D5.2671970_at_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 - 08:08:25 CST