# Max values on a group of fields

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.

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 ║
╚════╩════════╩════════╩════════╩════╝
``````
• Thank you very much, it worked like a charm. That's precisely what I was looking for. Apr 11, 2018 at 16:53