Selecting row with greatest value in one column

Question

Selecting row with greatest value in one column

Tracie Ann Williams

Old Hand

Points: 331
More actions
October 12, 2007 at 3:19 pm

#182652

Late on a Friday and I can't figure this one out...
I have a query that returns a derived table with values like this...
Loca Item# Total
2 931 89
3 330 910
3 933 209
3 919 100
4 931 260
4 924 89
I want to select from this derived table a single row for each location - the one with the highest total:
Loca Item# Total
2 931 89
3 330 910
4 931 260
Can't figure out the query... Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

This should get you started. You'll need to place your derived table query in place of the @DerivedTable variable in the FROM clause.

DECLARE @DerivedTable TABLE (Loca int, Item# int, Total int)

INSERT INTO @DerivedTable

SELECT 2, 931, 89 UNION ALL

SELECT 3, 330, 910 UNION ALL

SELECT 3, 933, 209 UNION ALL

SELECT 3, 919, 100 UNION ALL

SELECT 4, 931, 260 UNION ALL

SELECT 4, 924, 89

SELECT Loca, MAX(Total) as MaxTotal

FROM @DerivedTable

GROUP BY Loca

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Tracie Ann Williams Old Hand Points: 331 More actions · Answer 2

Tracie Ann Williams

Old Hand

Points: 331

October 12, 2007 at 3:36 pm

#741738

Thanks, but I need the item# number returned also...

John Rowan SSC Guru Points: 56440 More actions · Answer 3

This will do it, but with a derived table, you'll have to either define a temp table or table variable to hold the results, or have the derived table definition nested in your select twice.....you're call.

DECLARE @DerivedTable TABLE (Loca int, Item# int, Total int)

INSERT INTO @DerivedTable

SELECT 2, 931, 89 UNION ALL

SELECT 3, 330, 910 UNION ALL

SELECT 3, 933, 209 UNION ALL

SELECT 3, 919, 100 UNION ALL

SELECT 4, 931, 260 UNION ALL

SELECT 4, 924, 89

SELECT t1.*

FROM @DerivedTable t1

INNER JOIN (

SELECT Loca, MAX(Total) as MaxTotal

FROM @DerivedTable

GROUP BY Loca

) t2

ON t1.Loca = t2.Loca AND t1.Total = t2.MaxTotal

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Tracie Ann Williams Old Hand Points: 331 More actions · Answer 4

Thanks... used a table variable and it works great! Appreciate the help.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Be sure that the Total is unique for each location, otherwise you'll get into trouble..

Check here by adding one row as...

INSERT INTO @DerivedTable

SELECT 3, 931, 910

--Ramesh

Tracie Ann Williams Old Hand Points: 331 More actions · Answer 6

Good point... In this case, the query that creates the @table is a summarizaton so uniqueness will be there... Thanks...