Hi Group,
for several days I have been trying to resolve the following task below, but all my attempts so far appear to give me back too many rows:
I have data kind like this in TableA:
id IDone IDtwo sale
1 010 200 8000
2 010 200 7851
3 010 200 517
4 020 210 5730
5 020 210 2000
6 020 230 3170
7 020 230 3170
8 020 230 2051
9 030 230 0
With the query below I can basically select the maximum sale for each IDone - IDtwo combination - so almost what I need:
select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone, IDtwo;
But it would want to also select the id column ( and all other additonal 20 columns of TabelA not shown above) and need that there is only one record returned for each IDone - IDtwo combination. I tried
SELECT a.id, a.IDone, a.IDtwo, a.sale FROM TableA a
inner join ( select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone, IDtwo ) b
on a.IDone = b.IDone and b.IDtwo = b.IDtwo
and a.sale = b.maxsale;
but that returns too many rows and I do not understand why
How can I resolve this ?
Karsten Vennemann
Terra GIS LTD
