only each first item from a select with group ....

i want to get only the first (max sum ) value of each grouped item after a
select
example of the returned records where: two columns (grouped value company
and sum sell price)
in the table there are a nr of entrys of each company
i calculate the sum for the sell price in each company
i want to display only the best of a region
now i get somethink like
nord company33 145678,44
nord company2 34578,44
nord company44 478,44
nord company6 78,44
west company77 645678,44
west company8 35678,44
sud company9 995678,44
sud company4 678,44
but i want to display only the first (max) entry of each group - like
nord company33 145678,44 (the best from region nord)
west company77 645678,44
sud company9 995678,44
thanks
XavierTry,
To make it readable, let us create a view.
create view v1
as
here goes the select statement that does the grouping
go
select *
from v1 as a
where not exists (
select *
from v1 as b
where b.region = a.region
and b.sum_sell_price > a.sum_sell_price
)
-- or
select a.*
from v1 as a
inner join
(
select region, max(sum_sell_price ) as max_sum_sell_price
from v1
group by region
) as b
on a.region = b.region
and a.sum_sell_price = b.max_sum_sell_price
AMB
"Xavier" wrote:

> i want to get only the first (max sum ) value of each grouped item after
a
> select
> example of the returned records where: two columns (grouped value company
> and sum sell price)
> in the table there are a nr of entrys of each company
> i calculate the sum for the sell price in each company
> i want to display only the best of a region
> now i get somethink like
> nord company33 145678,44
> nord company2 34578,44
> nord company44 478,44
> nord company6 78,44
> west company77 645678,44
> west company8 35678,44
> sud company9 995678,44
> sud company4 678,44
> but i want to display only the first (max) entry of each group - like
> nord company33 145678,44 (the best from region nord)
> west company77 645678,44
> sud company9 995678,44
> thanks
> Xavier
>|||it works, thanks for your help
Xavier
"Alejandro Mesa" wrote:
> Try,
> To make it readable, let us create a view.
> create view v1
> as
> here goes the select statement that does the grouping
> go
> select *
> from v1 as a
> where not exists (
> select *
> from v1 as b
> where b.region = a.region
> and b.sum_sell_price > a.sum_sell_price
> )
> -- or
> select a.*
> from v1 as a
> inner join
> (
> select region, max(sum_sell_price ) as max_sum_sell_price
> from v1
> group by region
> ) as b
> on a.region = b.region
> and a.sum_sell_price = b.max_sum_sell_price
>
> AMB
> "Xavier" wrote:
>