Re: Simple query help - selecting row with highest total (for each name)

#1

On Tue, 23 Feb 2010 23:02:32 -0800, seven <s_e_v_e_n (AT) gawab (DOT) 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

Quote:

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

#2

worked like a charm

Thanks.

/7

Tom van Stiphout wrote:

Quote:

On Tue, 23 Feb 2010 23:02:32 -0800, seven <s_e_v_e_n (AT) gawab (DOT) 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

#3

This can be done easier using the ranking functions:

SELECT name, total_price, price_item_1, price_item_2, price_item_3
FROM (
SELECT name, total_price, price_item_1, price_item_2, price_item_3,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY total_price DESC) AS rk
FROM Foo) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com

#4

Even better !

thanks

/7

Plamen Ratchev wrote:

Quote:

This can be done easier using the ranking functions:

SELECT name, total_price, price_item_1, price_item_2, price_item_3
FROM (
SELECT name, total_price, price_item_1, price_item_2, price_item_3,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY total_price DESC) AS rk
FROM Foo) AS T
WHERE rk = 1;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Simple query help - selecting row with highest total (for each name)

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Simple query help - selecting row with highest total (for each name) - 02-24-2010 , 10:29 PM

Re: Simple query help - selecting row with highest total (for eachname) - 02-25-2010 , 01:34 AM

Re: Simple query help - selecting row with highest total (for eachname) - 02-25-2010 , 09:49 AM

Re: Simple query help - selecting row with highest total (for eachname) - 02-27-2010 , 09:51 AM