I need a query that selects rows in a table searching for the maximum value in a given column for each unique pair from two other columns. Consider this very simple example table:
a b c d
1 1 0 1
1 1 2 2
1 2 1 1
1 2 3 2
2 1 7 5
2 1 6 4
Unique pairs are formed from columns a and b. We look for the maximum in column d. The result returns the corresponding columns a, b and c. So, for the above sample data, the returned results should look like this:
a b c
1 1 2
1 2 3
2 1 7
I have tried nested queries and joins and I haven't reached my goal. I also performed an Google search looking for a discussion and consulted several database books, to no avail. I see plenty of examples of nested queries that involve multiple tables, but none that involve the same table.
It seems the problem requires three passes (nesting): 1) get a set of rows of unique a & b pairs 2) for each row in that set find the maximum value of d: return a set of a, b and d rows 3) for each triplet a, b, and d values, query returning a, b and c columns.
Help is much appreciated.
Aucun commentaire:
Enregistrer un commentaire