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

22 Sep 2005 8:26 PM

Xavier

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

22 Sep 2005 9:11 PM

Alejandro Mesa

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

Show quote

"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
>

23 Sep 2005 1:21 PM

Xavier

it works, thanks for your help
Xavier

Show quote

"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:
>
> >
> > 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
> >