MySQL How to select a date record between the min and max

Question

Say I have four dates, for example:

1/1/2018
2/5/2018
4/8/2018
9/9/2018

I want to 'select' a date in between the min and max. Either the 2/5/2018 record or the 4/8/2018 record ONLY.

I think I'm close, but this query returns ALL the rows:

SELECT * FROM RUBERIC R  
WHERE R.SCHOOLID = 75 
AND R.TEACHERID = 610 
AND R.OBSERVED = 'Observed Classroom' 
AND R.DATE BETWEEN 
(SELECT MIN(DATE) FROM RUBERIC WHERE R.SCHOOLID = 75 
AND R.TEACHERID = 610 AND R.OBSERVED = 'Observed Classroom' )
AND
(SELECT MAX(DATE) FROM RUBERIC WHERE R.SCHOOLID = 75 
AND R.TEACHERID = 610 AND R.OBSERVED = 'Observed Classroom' )

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Mar 17 '18 at 12:24

Gordon Linoff · Accepted Answer · 2018-03-17T12:10:48.727

BETWEEN includes the end points. Presumably, you want to exclude them:

SELECT R.*
FROM RUBERIC R  
WHERE R.SCHOOLID = 75 AND
      R.OBSERVED = 'Observed Classroom' AND
      R.DATE > (SELECT MIN(R2.DATE) FROM RUBERIC R2 WHERE R2.SCHOOLID = 75 
AND R2.TEACHERID = 610 AND R2.OBSERVED = 'Observed Classroom'
               ) AND
      R.DATE < (SELECT MAX(R2.DATE) FROM RUBERIC R2 WHERE R2.SCHOOLID = 75 
AND R2.TEACHERID = 610 AND R2.OBSERVED = 'Observed Classroom'
               );

Also, your query is -- inadvertently -- a correlated subquery. The table alias R refers to the rows in the main query, not the subquery. You should always define table aliases and always use them, but use them properly. (Note all the R2s in the subqueries in this version.)

In general, I strongly discourage anyone from using BETWEEN with date or date/times. This is not a problem in your case, but the time component can lead to unexpected results.

Thanks, Gordon. The table aliases make sense. This removed the MIN and MAX records. Now, I'm trying to get it to select ONE of the 2 'middle' records. — JWK, Mar 17 '18 at 12:21

score 0 · Answer 2 · answered Mar 17 '18 at 11:58

0

This is due to the fact that BETWEEN is inclusive. See this answer.

answered Mar 17 '18 at 11:58

Daryl Bagley

17
6

score 0 · Answer 3 · answered Mar 17 '18 at 12:24

Your BETWEEN condtition doesn't change anything on the result set, since MIN and MAX will be included. You need to split the BETWEEN condition into < and > conditions. But in order to not duplicate the conditions on SCHOOLID, TEACHERID and OBSERVED, I would first select the min and max date in a subquery and then join the table to it.

SELECT R.*
FROM (
    SELECT SCHOOLID, TEACHERID, OBSERVED, MIN(DATE) AS MIN_DATE, MAX(DATE) AS MAX_DATE
    FROM RUBERIC
    WHERE SCHOOLID  = 75 
      AND TEACHERID = 610 
      AND OBSERVED  = 'Observed Classroom'
) X
JOIN RUBERIC R
  ON  R.SCHOOLID  = X.SCHOOLID
  AND R.TEACHERID = X.TEACHERID
  AND R.OBSERVED  = X.OBSERVED
  AND R.DATE      > X.MIN_DATE
  AND R.DATE      < X.MAX_DATE

If you need to select "any" one of the rows, just add LIMIT 1 at the end of the query.

MySQL How to select a date record between the min and max

3 Answers3