Return preferred record when there is more than one record for the same user-CodePudding

I have a table where it stores the types of discounts that a user can have.

Some users will get the standard discount, but some will get a bigger and better discount. For users who have the biggest and best discount, there will be two records in the database, one for the default discount and the other for the biggest and best discount. The biggest and best discount will be preferred in the search.

I would like to do a SELECT that would return the record with the highest discount and if you don't find it, return it with the standard discount for me to avoid making two queries in the database or having to filter in the source code.

Ex:

|  id  |  user_id  |  country  |  discount  |  cashback  |  free_trial  |
|-----------------------------------------------------------------------|
|  1   |     1     |    EUA    |   DEFAULT  |     10     |     false    |
|  2   |     1     |    EUA    |  CHRISTMAS |     20     |     true     |
|  3   |     3     |    EUA    |   DEFAULT  |     10     |     false    |

SELECT *
FROM users
WHERE country = 'EUA'
AND (discount  = 'CHRISTMAS' OR discount = 'DEFAULT');

In this example above for user 1 it would return the record with the discount equal to "CHRISTMAS" and for user 3 it would return "DEFAULT" because it is the only one that has. Can you help me please?

CodePudding user response：

You can use the row_number() window function to do this. This function includes a PARTITION BY that lets you start the numbering over with each user, as well as it's own ORDER BY that lets you determine which rows will sort first within each user/partition.

Then you nest this inside another SELECT to limit to rows where the row_number() result is 1 (the discounted that sorted best):

SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY id, ORDER BY cashback desc) rn
    FROM users
    WHERE country = 'EUA'
) u
WHERE rn = 1

You could also use a LATERAL JOIN, which is usually better than the correlated join in the other answer, but not as good as the window function.

CodePudding user response：

You can using GROUP BY to do it

SELECT u1.*
 FROM users u1
JOIN
    ( 
        SELECT COUNT(id) AS cnt,user_id
         FROM users WHERE country = 'EUA'
        GROUP BY  user_id
    ) u2 ON u1.user_id=u2.user_id
WHERE IF(u2.cnt=1,u1.discount='DEFAULT',u1.discount='CHRISTMAS')

DB Fiddle Demo