TSQL - Picking up first match from a group of rows

Question

I have a simple scenario wherein, a table stores data about which card(s) a users uses and if those cards are registered (exist) in the system. I've applied ROW_NUMBER to group them too

SELECT User, CardId, CardExists, ROW_NUMBER() OVER (PARTITION BY User) AS RowNum From dbo.CardsInfo

User   | CardID | CardExists | RowNum
-------------------------------------
A      | 1      |   0        |  1
A      | 2      |   1        |  2
A      | 3      |   1        |  3
---------------------------------
B      | 4      |   0        |  1
B      | 5      |   0        |  2
B      | 6      |   0        |  3
B      | 7      |   0        |  4
---------------------------------
C      | 8      |   1        |  1
C      | 9      |   0        |  2
C      | 10     |   1        |  3

Now in the above, I need to filter out User cards based on the two rules below

1. If in the cards registered with a user, multiple cards exist in the system, then take first one. So, for user A, CardID 2 will be returned and for User C it'll return CardID = 8
2. Othwerwise, if no card is existing (registered) for the user in the system, then just take the first one. So, for user B, it should return CardID = 4

Thus, final returned set should be -

User   | CardID | CardExists | RowNum
-------------------------------------
A      | 2      |   1        |  2
---------------------------------
B      | 4      |   0        |  1
---------------------------------
C      | 8      |   1        |  1

How can I do this filteration in SQL?

Thanks

Answer 1

You can use:

SELECT ci.*
FROM (SELECT User, CardId, CardExists,
             ROW_NUMBER() OVER (PARTITION BY User ORDER BY CardExists DESC, CardId) AS RowNum
      FROM dbo.CardsInfo ci
     ) ci
WHERE seqnum = 1;

You can also do this with aggregation:

select user,
       max(cardexists) as cardexists,
       coalesce(min(case when cardexists = 1 then cardid end),
                min(card(cardid)
               ) as cardid
from cardsinfo
group by user;

Or, if you have a separate users table:

select ci.*
from users u cross apply
     (select top (1) ci.*
      from cardinfo ci
      where ci.user = u.user
      order by ci.cardexists desc, cardid asc
     ) ci