Prev: Place a message in the outbox of Outlook
Next: Split a period of time
From: seven on 24 Feb 2010 02:02
> Guys,
>
> newbie on SQL needs help on a query..
>
> > Name Total_Price Price _item_1 Price _item_2 Price _item_3
> > A 500 99 300 101
> > A 700 89 211 200
> > A 900 10 20 870
> > B 100 11 9 80
> > B 500 50 300 150
> > B 300 100 100 100
> > B 400 25 450 25
> >
> >
> > Result of Query
> >
> > Name Total_Price Price _item_1 Price _item_2 Price _item_3
> > A 900 10 20 870
> > B 500 50 300 150
>
> I need to pull all the columns of the row with the highest total Total
> price for all the names
>
> I have attached the excel sheet also
>
> Thanks for all the help
>
> /7
From: Tom van Stiphout on 24 Feb 2010 23:29
On Tue, 23 Feb 2010 23:02:32 -0800, seven <s_e_v_e_n(a)gawab.com> wrote:

The way I solve this is by first finding the max rows:
select T2.Name, MAX(T2.Total_Price) as Total_Price
from dbo.myTable T2
group by T2.Name

Then join this with the table to get the entire rows:
elect T.* from
(select T2.Name, MAX(T2.Total_Price) as Total_Price
from dbo.myTable T2
group by T2.Name) T3
inner join dbo.myTable T on T3.Name = T.Name and T3.Total_Price =
T.Total_Price

-Tom.
Microsoft Access MVP




> > Guys,
> >
> > newbie on SQL needs help on a query..
> >
> > > Name Total_Price Price _item_1 Price _item_2 Price _item_3
> > > A 500 99 300 101
> > > A 700 89 211 200
> > > A 900 10 20 870
> > > B 100 11 9 80
> > > B 500 50 300 150
> > > B 300 100 100 100
> > > B 400 25 450 25
> > >
> > >
> > > Result of Query
> > >
> > > Name Total_Price Price _item_1 Price _item_2 Price _item_3
> > > A 900 10 20 870
> > > B 500 50 300 150
> >
> > I need to pull all the columns of the row with the highest total Total
> > price for all the names
> >
> > I have attached the excel sheet also
> >
> > Thanks for all the help
> >
> > /7
 | 
Pages: 1
Prev: Place a message in the outbox of Outlook
Next: Split a period of time