CODEWITHSUNDEEP
sqlsql-server
Phil Sandler
Phil Sandler

Reputation: 28016

SQL MAX(column) With Additional Criteria

I have a single table, where I want to return a list of the MAX(id) GROUPed by another identifier. However I have a third column that, when it meets a certain criteria, "trumps" rows that don't meet that criteria.

Probably easier to explain with an example. Sample table has:

UniqueId (int) GroupId (int) IsPriority (bit)

Raw data:

UniqueId    GroupId    IsPriority
-----------------------------------
    1           1          F
    2           1          F
    3           1          F
    4           1          F
    5           1          F
    6           2          T
    7           2          T
    8           2          F
    9           2          F
   10           2          F

So, because no row in groupId 1 has IsPriority set, we return the highest UniqueId (5). Since groupId 2 has rows with IsPriority set, we return the highest UniqueId with that value (7).

So output would be:

5
7

I can think of ways to brute force this, but I am looking to see if I can do this in a single query.

Upvotes: 3

Views: 137

Answers (1)

Martin Smith
Martin Smith

Reputation: 453027

SQL Fiddle Demo

WITH T
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY GroupId 
                                    ORDER BY IsPriority DESC, UniqueId DESC ) AS RN
         FROM   YourTable)
SELECT UniqueId,
       GroupId,
       IsPriority
FROM   T
WHERE  RN = 1

Upvotes: 7

Related Questions