1

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.

4

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. – Fábio Apr 11 '18 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.