Max values on a group of fields

Question

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.

Answer 1

If I understood this correctly, you can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *, 
            RN = ROW_NUMBER() OVER(PARTITION BY fieldA, fieldB ORDER BY fieldC DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE 
WHERE RN = 1
;

Here is a demo of this.

The results are:

╔════╦════════╦════════╦════════╦════╗
║ ID ║ fieldA ║ fieldB ║ fieldC ║ RN ║
╠════╬════════╬════════╬════════╬════╣
║  3 ║ A      ║ X      ║      3 ║  1 ║
║  4 ║ A      ║ Y      ║      4 ║  1 ║
║  6 ║ B      ║ Y      ║      2 ║  1 ║
║  9 ║ B      ║ Z      ║      5 ║  1 ║
╚════╩════════╩════════╩════════╩════╝