Can SQL IN() Query TAKE OR statement

Question

I have a table name data as shown Below. the column mn runs from 1 to 59 and starts again from 1 so i have a query that selects 10 interval like this

SELECT * FROM data WHERE mn IN('00','10','20','30','40','50') AND dt='2019-04-20' ORDER BY ID ASC

this works fine and give the desired result. But for some reason sometimes this wanted data 00,10,20,30 etc are not available i want to be able to pick the next or the one before in place of it.

Lets say 10 is not available i want to be able to pick 09 or 11 in its place, or lets say 20 is not available i want to be able to pick 19 or 21 in its place. How can i do this i tried an OR in the IN statement it returned funny result. Please any help will be greatly appreciated Thanks

id  mn  dt         status
---|---|----------|-----------
 1 |01 |2019-04-20|1    
 2 |02 |2019-04-20|1
 3 |03 |2019-04-20|1    
 4 |04 |2019-04-20|1
 5 |05 |2019-04-20|1    
 6 |06 |2019-04-20|1
 7 |07 |2019-04-20|1    
 8 |08 |2019-04-20|1
 9 |09 |2019-04-20|1    
 10|10 |2019-04-20|1
 11|11 |2019-04-20|1    
 12|12 |2019-04-20|1
 13|13 |2019-04-20|1    
 14|14 |2019-04-20|1
 15|15 |2019-04-20|1    
 16|16 |2019-04-20|1
 17|17 |2019-04-20|1    
 18|18 |2019-04-20|1
 19|19 |2019-04-20|1    
 20|21 |2019-04-20|1
 21|22 |2019-04-20|1    
 22|23 |2019-04-20|1

What if 9, 10 and 11 are all not available? Is that a possible scenario? — Nick, Apr 27 '19 at 22:54
If 9 ,10, 11 are not available then it will be ignored i just need to be able to pick one before or one after that's all @nick — Donsplash, Apr 27 '19 at 22:59
Could you explain what ’mn’ is? If it is a foreign key to a list of something, then you have something to work with using left join and coalesce. If this is a flat file it’ll be much harder. — Tim Morton, Apr 27 '19 at 23:03
Just to confirm, somewhere around `id = 60` and `id = 120` etc., `mn` will revert to `1` and start counting up again? — Nick, Apr 27 '19 at 23:04
yes Nick because mn is actually Minutes so from 59 it goes to 00 then 01 etc @Nick — Donsplash, Apr 27 '19 at 23:22
Is there another column which is the hour so you can distinguish the different minute records? — Nick, Apr 27 '19 at 23:23
@Donsplash In your sample data the date changes as the minute increases, is that normal? or would you get all the hours and minutes from one date, followed by the hours and minutes from the next date, etc.? — Nick, Apr 27 '19 at 23:26
Your sample data really doesn't make sense. You have rows back to back that appear to be connected, but represent entirely different days. Is this a real example? Why is ordering by ID valuable? — gview, Apr 27 '19 at 23:30
@Nick Sorry About that its not a real example the date are suppose to run normally i just used that little data to explain my question my bad sorry for that Ordering is not actually valuable — Donsplash, Apr 27 '19 at 23:32
@Donsplash without some way of ordering the data I don't think it's possible to solve your problem. — Nick, Apr 27 '19 at 23:35
@Nick you can order by id ASC because time moves from 0 to 60 so you can order with id because this data come in every minute but for some reason sometimes it dont come for 10 or 20 or even 30 and so on. so i want to be able to use that data that came a minute earlier or after that the whole idea — Donsplash, Apr 27 '19 at 23:38

Nick · Accepted Answer · 2019-05-09T05:24:24.303

Here's a query that I think will give you the results you want. It uses a derived table of the minute values with the minimum difference between them and the nearest multiple of 10 minutes. To differentiate between 9 and 11, we add mn % 10 / 10, so the results of this query are (e.g. for minutes = 8, 9, 10, 11, 12): 2.8, 1.9, 0, 1.1, 2.2. We discard values > 2 as they should be ignored. This derived table is then JOINed to the table to select the data from the corresponding row:

SELECT d.*
FROM data d
JOIN (SELECT IF(hr = 23 AND mn = 59, dt + INTERVAL 1 DAY, dt) AS date,
             IF(mn = 59, (hr + 1) % 24, hr) AS hour,
             ((mn + 1) DIV 10) % 6 AS mn10, 
             MIN(LEAST(ABS(mn - mn DIV 10 * 10), ABS(mn - (mn + 9) DIV 10 * 10)) + (mn % 10 / 10)) AS mndiff
      FROM data
      GROUP BY date, hour, mn10
      HAVING mndiff < 2) dd
  ON dd.date = IF(d.hr = 23 AND d.mn = 59, d.dt + INTERVAL 1 DAY, d.dt)
 AND dd.hour = IF(d.mn = 59, (d.hr + 1) % 24, d.hr)
 AND dd.mn10 = ((d.mn + 1) DIV 10) % 6
 AND dd.mndiff = LEAST(ABS(d.mn - d.mn DIV 10 * 10), ABS(d.mn - (d.mn + 9) DIV 10 * 10)) + (d.mn % 10 / 10)

Output (for my demo on dbfiddle)

id  mn  hr  dt          status
2   50  23  2019-04-19  1
12  1   0   2019-04-20  1
20  11  0   2019-04-20  1
27  19  0   2019-04-20  1

Update

In the case where, for example, data for minutes 9 and 11 are available (but 10 is not) this query will favour the value from minute 11. That can be reversed by changing

 + (mn % 10 / 10)

to:

 - (mn % 10 / 10)

and

HAVING mndiff < 2

to:

HAVING mndiff < 1

So the modified query is:

SELECT d.*, dd.*
FROM data d
JOIN (SELECT IF(hr = 23 AND mn = 59, dt + INTERVAL 1 DAY, dt) AS date,
             IF(mn = 59, (hr + 1) % 24, hr) AS hour,
             ((mn + 1) DIV 10) % 6 AS mn10, 
             MIN(LEAST(ABS(mn - mn DIV 10 * 10), ABS(mn - (mn + 9) DIV 10 * 10)) - (mn % 10 / 10)) AS mndiff
      FROM data
      GROUP BY date, hour, mn10
      HAVING mndiff < 1) dd
  ON dd.date = IF(d.hr = 23 AND d.mn = 59, d.dt + INTERVAL 1 DAY, d.dt)
 AND dd.hour = IF(d.mn = 59, (d.hr + 1) % 24, d.hr)
 AND dd.mn10 = ((d.mn + 1) DIV 10) % 6
 AND dd.mndiff = LEAST(ABS(d.mn - d.mn DIV 10 * 10), ABS(d.mn - (d.mn + 9) DIV 10 * 10)) - (d.mn % 10 / 10)

Output for my demo for this query is:

id  mn  hr  dt          status
2   50  23  2019-04-19  1
11  59  23  2019-04-19  1
20  11  0   2019-04-20  1
27  19  0   2019-04-20  1

As you can see, it has preferred the 59 value over the 01 value.

Updated dbfiddle

In terms of only selecting values that occur at or before the 10 minute mark (e.g. 10,9,8,7 in order of precedence), you can simplify the query to this:

SELECT d.*
FROM data d
JOIN (SELECT IF(hr = 23 AND mn >= 57, dt + INTERVAL 1 DAY, dt) AS date,
             IF(mn >= 57, (hr + 1) % 24, hr) AS hour,
             ((mn + 3) DIV 10) % 6 AS mn10, 
             MIN(ABS(mn - (mn + 9) DIV 10 * 10)) AS mndiff
      FROM data
      GROUP BY date, hour, mn10
      HAVING mndiff <= 3) dd
  ON dd.date = IF(d.hr = 23 AND d.mn >= 57, d.dt + INTERVAL 1 DAY, d.dt)
 AND dd.hour = IF(d.mn >= 57, (d.hr + 1) % 24, d.hr)
 AND dd.mn10 = ((d.mn + 3) DIV 10) % 6
 AND dd.mndiff = ABS(mn - (mn + 9) DIV 10 * 10)

Output for my demo for this query is:

id  mn  hr  dt          status
2   50  23  2019-04-19  1
11  59  23  2019-04-19  1
19  8   0   2019-04-20  1
27  19  0   2019-04-20  1
33  27  0   2019-04-20  1

Updated dbfiddle

Update 2

Based on additional feedback in comments, the time is being displayed as the time of the nearest 10 minute marker rather than the raw time. This results in minor changes to the query:

SELECT dd.mn10 * 10 AS mn, dd.hour, dd.date, d.status, d.id
FROM data d
JOIN (SELECT IF(hr = 23 AND mn >= 57, dt + INTERVAL 1 DAY, dt) AS date,
             IF(mn >= 57, (hr + 1) % 24, hr) AS hour,
             ((mn + 3) DIV 10) % 6 AS mn10, 
             MIN(ABS(mn - (mn + 9) DIV 10 * 10)) AS mndiff
      FROM data
      GROUP BY date, hour, mn10
      HAVING mndiff <= 3) dd
  ON dd.date = IF(d.hr = 23 AND d.mn >= 57, d.dt + INTERVAL 1 DAY, d.dt)
 AND dd.hour = IF(d.mn >= 57, (d.hr + 1) % 24, d.hr)
 AND dd.mn10 = ((d.mn + 3) DIV 10) % 6
 AND dd.mndiff = ABS(mn - (mn + 9) DIV 10 * 10)
 ORDER BY dd.date, dd.hour, mn

Output from updated demo

mn  hour    date        status  id
0   23      2019-04-19  1       12
50  23      2019-04-19  1       2
0   0       2019-04-20  1       11
10  0       2019-04-20  1       20
20  0       2019-04-20  1       28
30  0       2019-04-20  1       34

thanks i have been ill that's why you didn't hear from me This Query Will Help a lot and i will mark it . But Nick i noticed that the query favors most after and not before . for example if 9 and 11 is available it picks 11 first instead of 9. but i think there could be a work around. And again WHat if i am only considering Just only before and no after Lets say 7, 8, 9 can be picked if 10 id not available? — Donsplash, May 04 '19 at 21:42
@Donsplash Sorry to hear it - hope you're fully recovered. I've updated my post with two new queries: the first will favour the 9 value over 11, and the second will only pick from before (10, 9, 8, 7 in that order of precedence), not afterwards. — Nick, May 05 '19 at 05:09
Nick Your solution works Thanks But one last question and help. Thanks for asking i am better now. now Nick if i select let say 7 in place of 10. how do i still make it show me 10 as the mn field but will use the value of 7, and in the case of 20 maybe i used 19 in place of 20 since 20 was not available how can i return the result to show 20 and use the value of 19 as the case maybe. And finally can i edit the last query to mn >= 52 if i wan to accommodate up to let say 456789 Thanks Nick for your Help. — Donsplash, May 07 '19 at 10:00
@Donsplash in terms of your first question, just change `SELECT d.*` to `SELECT dd.mn10 * 10 AS mn, d.hr, d.dt, d.status`. I'm not sure what you mean by the second one? What does the hour field look like if you have mn going up to a large number? — Nick, May 07 '19 at 22:37
Hello Nick When i did This as in changed SELECT d.* to SELECT dd.mn10 * 10 AS mn, d.hr, d.dt, d.status i got an Error Invalid column dd on field List. then again when i mean on the second question was that if i can accommodate for values 4, 5, 6, 7, 8, 9 in case 10 or any other closer value is not available thanks for your help so far . — Donsplash, May 08 '19 at 06:21
@Donsplash not sure why you're having a problem with the new query see this fiddle: https://www.db-fiddle.com/f/tNfkPkD9ztZkCH2P54UZHX/5 — Nick, May 08 '19 at 06:23
In terms of allowing for 4,5,6,7,8,9, in the last query you need to change `57` to 50 plus the lowest one you want as a replacement, and 3 in `mndiff <= 3` and `mn + 3` to 10 - the lowest one you want as a replacement. So if you want 4 as the lowest replacement, you would replace `57` with `54` and `3` with `6`. — Nick, May 08 '19 at 06:26
Hello Nick thanks A million if i were in Australia i would Buy you a Beer Take a look at this https://www.db-fiddle.com/f/tNfkPkD9ztZkCH2P54UZHX/6 Run it and see 0 0 appearing twice how do we take it out it keep appearing twice that is 00, 00 then before 10, 20, 30,40 ,50 then again 00, 00 . what i noticed is that even if 00 which is 60 is available it still takes 59 and use it as 00 thats why its coming twice. — Donsplash, May 08 '19 at 09:38
Hello Nick please help take a look at this https://www.db-fiddle.com/f/tNfkPkD9ztZkCH2P54UZHX/6 and see why its having behavior i mentioned above? — Donsplash, May 08 '19 at 15:11
@Donsplash it's caused by the data value you have in the list which is `('00', 23, '2019-04-19', '1')` but should be `('00', 0, '2019-04-20', '1')`. But also the `SELECT` needs to be updated (it should be displaying the adjusted values from `dd` instead of the raw values from `d`). If you also display the `id` values you can see it's doing the right thing. See https://www.db-fiddle.com/f/tNfkPkD9ztZkCH2P54UZHX/8 — Nick, May 09 '19 at 05:21
Hello Nick please i need a little explanation on some parts of this query for some reason when i try it on Big database it acts funny. on your first answer you used this LEAST(ABS(d.mn - d.mn DIV 10 * 10), ABS(d.mn - (d.mn + 9) DIV 10 * 10)) + (d.mn % 10 / 10) and subsiquently on the other two MIN(ABS(mn - (mn + 9) DIV 10 * 10)) AS mndiff. then again this (mn - (mn + 9) DIV 10 * 10) will always give you 9 no matter what the value of of mn. i am trying to understand the query better so i know how to adjust it on this big data. Thanks Nick — Donsplash, May 13 '19 at 16:38
@Donsplash the `LEAST` part was to give me a distance measure from the 10 minute marker. It gives 1 for 9 and 1, 2 for 8 and 2, 3 for 7 and 3 etc. This allows the query to select the closest value to the 10 minute mark by taking the `MIN` of those values. In the subsequent queries you only wanted values prior to the 10 minute mark so I didn't need something so complicated and I used the other formula which also gives a distance from 10 but set up so that 9 is closest, followed by 8, 7, 6 etc. See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=69622a0f8d0224beff7927d1fde199a8 — Nick, May 14 '19 at 12:58
Hello Nick Please take a look at this DB https://www.db-fiddle.com/f/kPoL1tkZtowAG6YfAKWafQ/0 you will discover that its not working well. just try for both user1 and User2 and for both in WHERE clause you will see. please help on the Query Thanks For Example at ID 20 only user2 has something on 10mins while user one had something on 9mins it didnt pick 9 since 10 was not available. You will see this happening in so many parts downwards. — Donsplash, May 17 '19 at 00:23
@Donsplash you need to add the `WHERE` condition into the subquery too: https://www.db-fiddle.com/f/kPoL1tkZtowAG6YfAKWafQ/1 or alternatively add `user` to the subquery `GROUP BY` and join condition: https://www.db-fiddle.com/f/kPoL1tkZtowAG6YfAKWafQ/2 — Nick, May 17 '19 at 00:36

Can SQL IN() Query TAKE OR statement

1 Answers1