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

How can i pick which row is selected when using OR in Mysql

I have a table in MySQL that holds rows of days from 1 to 7 (Monday to Sunday), i want to get a full week of rows to 1 to 7 based on a specific date the date is null in some rows and set in some rows:

id day date
1 1 NULL
2 2 NULL
3 3 NULL
4 4 NULL
5 5 NULL
6 6 NULL
7 7 NULL
8 3 2024-05-29

i would like to select the week and it should include the specific date so for example selecting the week based on 2024-05-29 should give me this result:

id day date
1 1 NULL
2 2 NULL
8 3 2024-05-29
4 4 NULL
5 5 NULL
6 6 NULL
7 7 NULL

and if a select a date that doesn’t exist like 2024-05-05 it should give me:

id day date
1 1 NULL
2 2 NULL
3 3 NULL
4 4 NULL
5 5 NULL
6 6 NULL
7 7 NULL

is this possible to do with a single query? otherwise how would you go about doing something like this? thank you

edit: im on Mysql version 8.0

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is the query that i came up with which does work exactly how i wanted it, if anyone has any remarks on how to optimize this or any other solution i would love to hear it:

    SELECT s1.* FROM my_table as s1 INNER JOIN ( SELECT day, MAX(date) as date_max FROM my_table WHERE my_table.date = '2024-06-29' OR my_table.date is null GROUP BY my_table.day ) as s2 ON s1.day = s2.day AND ( s1.date = s2.date_max or ( s1.date IS NULL AND s2.date_max IS NULL ) ) ORDER BY day ASC

  2. 0

    Group by day, use a conditional to return NULL when the date column doesn’t match the given date, otherwise the date column. Then use MAX() to prefer that row.

    SELECT day, MAX(IF(WEEKDAY('2024-05-29')+1 = day, date, NULL)) AS date
FROM dates
GROUP BY day;

SELECT day, MAX(IF(WEEKDAY('2024-05-05')+1 = day, date, NULL)) AS date
FROM dates
GROUP BY day;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search