max query 1

[bouwob]

I shoulnt have to ask this but my mind just doesnt want to give me an answer tonight. I have this data
a b c
94070 9764 scacggwla
94070 26840 rmansdick
179741 16207 schedgllu
179741 28738 scherellu
221280 0 bbmigwels

what I need returened is
94070 26840 rmansdick
179741 28738 scherellu
221280 0 bbmigwels


select
distinct(1), c
from
table
where a in (select a from table1)
group by c
having b = max(b)

Now the problem is that the distinct(a) can not pull a distinct since other columns in the select are different. I am totally out of ideas and am frankly embarrised about it. Any help?

 

[gmmastros]

This can be done in a single query. Here's how...

First, write a query that returns the A column, and the Max value from the b column.

[blue][tt]Select a, Max(b) As MaxB
From Table
Group By a[/tt][/blue]

Then, use this query as a derived table to join back to the original table. Use both columns from the 'Max' query to join to the original table.

[tt][green]Select Table.A, Table.B, Table.C
From Table
Inner Join (
[blue][tt]Select a, Max(b) As MaxB
From Table
Group By a[/tt][/blue]
) As MaxValues
On Table.A = MaxValues.A
And Table.B = MaxValues.MaxB[/green][/tt]

Notice how the blue section of code is the same as the original query. This is now a derived table. As such, you need to have parenthesis around it, and also give it an alias.

Be aware that you could get duplicates from this query that a distinct may not sure. This would only happen if there are multiple matching 'a' rows, that have a corresponding max 'b' value, but the c columns are different.

For example... if your original data was...

94070 9764 scacggwla
94070 26840 rmansdick
94070 26840 Kablooie

The query I show would return 2 rows for 94070,26840. one for rmansdick and another for Kablooie.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bouwob]

that did it. It was the group by that was getting me the whole time. Thanks for the help and the heads up.