CODEWITHSUNDEEP
mysqlsql
NutellaAddict
NutellaAddict

Reputation: 574

MySQL SELECT values within range AND next values outside range in one Query

Hello I have the following MySQL query:

SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh 
FROM rangeData 
WHERE rangeStartTime >= 0 
AND rangeEndTime <= 43200 
AND (sensorNumber = 3)
ORDER BY sensorNumber;

How can I make it such that I can grab just 1 previous and next values outside the rangeStartTime and rangeEndTime time span?

So in english this would be said as: "Select all values within 5:00 pm and 7:00 pm, also select next value right before 5:00 pm and next value right after 7:00 pm."

I tried the following by looking at other examples but it did not work:

SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh 
FROM rangeData 
WHERE rangeStartTime >= 0 
AND rangeEndTime <= 43200 
AND rangeStartTime < 0 LIMIT 1
AND rangeEndTime > 43200 LIMIT 1
AND (sensorNumber = 3)
ORDER BY sensorNumber;

Each query adds 5-10 seconds of wait time so I would like to make this into one query.

Upvotes: 2

Views: 686

Answers (1)

uri2x
uri2x

Reputation: 3202

You could do that with a simple union of 3 queries, your original one, one for the previous and one for the next:

(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh 
FROM rangeData 
WHERE (rangeStartTime BETWEEN 0 AND 43200) AND (sensorNumber = 3))
UNION
(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh 
FROM rangeData 
WHERE (rangeStartTime < 0) AND (sensorNumber = 3)
ORDER BY rangeStartTime DESC
LIMIT 1)
UNION
(SELECT sensorNumber, rangeStartTime, rangeEndTime, sensorLow, sensorAverage, sensorHigh 
FROM rangeData 
WHERE (rangeStartTime > 43200) AND (sensorNumber = 3)
ORDER BY rangeStartTime ASC
LIMIT 1)
ORDER BY sensorNumber;

(BTW: The ORDER BY is not needed as you're querying for only one sensorNumber)

Upvotes: 3

Related Questions