Question 113456 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL max row value other than null

TABLE1

mach_id optout time
16543 NULL 2022-09-27
16543 YES 2022-09-26
16543 NO 2022-09-25
14444 YES 2022-09-27
15676 NO 2022-09-27
15676 YES 2022-09-26
11111 NULL 2022-09-27
11111 NULL 2022-09-26

Required table

mach_id optout
16543 YES
14444 YES
15676 NO
11111 NULL

I want to get the max optout value other than null if there are any other values for a mach_id based on time. If every optout value is null for mach_id optout should be null.
How can I query it?

I wrote this query but it’s not working

  Select max(COALESCE(OPTOUT, 0)) AS optout,mach_id
from
(select OPTOUT,mach_id, time from table1)
where 
time=max(time)
group by mach_id
Tags:

2

Answers


  1. 0

    We can use ROW_NUMBER here:

    WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY mach_id
                                   ORDER BY optout IS NULL, time DESC) rn
    FROM table1 t
)

SELECT mach_id, optout
FROM cte
WHERE rn = 1;

    The sort in the call to ROW_NUMBER places, for each group of mach_id records, the non null optout values first. Within those subgroups, the record with the latest time is chosen. A null optout record can only appear in the result set if only such null records are available.

    Login or Signup to reply.
  2. 0

    It is possible to achieve it using QUALIFY:

    SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY mach_id 
                          ORDER BY optout IS NOT NULL DESC, time DESC) = 1;

    Output:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search