January 6, 2006 at 7:48 am
hello
I have a table with 3 fields A,B,C:
example
A B C
10 10 50
10 20 40
10 30 90
20 10 60
20 30 75
I want to select the value of C for the highest B within a group by A:
for example with these values I would expect the result :
A C
10 90
20 75
chris
January 6, 2006 at 8:11 am
In the future, please provide DDL and sample data
See reasons and instruction at http://www.aspfaq.com/etiquette.asp?id=5006
create table MyTable
(a integer not null
, b integer not null
, c integer not null
)
insert into MyTable
(A,B,C)
select 10, 10, 50 union all
select 10, 20, 40 union all
select 10, 30, 90 union all
select 20, 10, 60 union all
select 20, 30, 75
select MyTable.A
, MyTable.C
from MyTable
join (select A, max(b)
from MyTable
group by A)
as MyTable_LargestB (A, B)
on MyTable_LargestB.A = MyTable.A
and MyTable_LargestB.B = MyTable.B
order by MyTable.A
, MyTable.C
SQL = Scarcely Qualifies as a Language
January 6, 2006 at 8:13 am
thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply