jeudi 13 août 2015

SQLite SQL query - 3 level/nesting on same table?

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