Does anyone know how can I select rows that contain maximum value for some group of fields based on some criteria?
For instance, let's say I have the following table:
Table1: ID fieldA fieldB fieldC 1 A X 1 2 A X 2 3 A X 3 4 A Y 4 5 A Y 1 6 B Y 2 7 B Z 3 8 B Z 4 9 B Z 5
Let's say that I want a table with results of maximum values of fieldC for each different entry of fieldA where fieldB=X, fieldB=Y, fieldB=Z and so on. The result should be:
ID fieldA fieldB fieldC 3 A X 3 4 A Y 4 6 B Y 2 9 B Z 5
I know how to do it for one criteria, but not for a set of criteria:
SELECT fieldA, MAX(fieldC) AS maxFieldC FROM table1 WHERE fieldA = 'A' GROUP BY ID
I tried a
UNION clause, but I couldn't make it work.