Geek asks and answers

A combination of two queries that require a specific SORT and LIMIT

I have a table in which there is a date field (schedule_date) and a varchar field (schedule time) in which the number of hours (0..23) is a string.

I have two queries:

Request 1) Returns any upcoming schedules :
a) everything with a date> today
b) And everything where the date corresponds to the current date, but to the hour> current hour

Request 2) Returns 1 record - time of the last run

I tried to figure out how to combine the two, but I'm not sure how to deal with the two problems that go past a simple union. I assume the subqueries will be involved, but I'm at the limit of my SQL skills here.

Problem 1): Only the second query requires "LIMIT 1" - how do I apply only query 2?

Problem 2): For the second query, DESC order is required for LIMIT 1 to work correctly, but I need the results of both queries combined in ASC order

Note: adding + 1 hour to the current time below (storage dates are specified in EST, the server returns CST for NOW)

QUERY 1: Return any upcoming schedules

SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date > DATE(NOW())
UNION 
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date = DATE(NOW())
AND CONVERT(schedule_time, DECIMAL) > (HOUR(CURRENT_TIME()) + 1)

QUERY 2: return of the last reversal chart (it is required that the DESC order for LIMIT 1 be in the last schedule)

SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date = DATE(NOW())
AND CONVERT(schedule_time, DECIMAL) < (HOUR(CURRENT_TIME()) + 1)
UNION
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date < DATE(NOW())
ORDER BY schedule_date DESC, schedule_time DESC
LIMIT 1

edit: fixed ORDER above (should have been DESC)

When joining, you need to sort the results of both joins in ASC:

ORDER BY schedule_date ASC, schedule_time ASC

Thank!

+3
source share
2 answers

, , :

SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date > DATE(NOW())
UNION 
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date = DATE(NOW())
AND CONVERT(schedule_time, DECIMAL) > (HOUR(CURRENT_TIME()) + 1)
UNION ALL
(
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date = DATE(NOW())
AND CONVERT(schedule_time, DECIMAL) < (HOUR(CURRENT_TIME()) + 1)
UNION
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date < DATE(NOW())
ORDER BY schedule_date DESC, schedule_time DESC
LIMIT 1
)
ORDER BY schedule_date ASC, schedule_time ASC

, UNION SELECT, OR -ed, :

  • SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date > DATE(NOW())
   OR schedule_date = DATE(NOW()) AND CONVERT(schedule_time, DECIMAL) > HOUR(CURRENT_TIME())

  • SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date < DATE(NOW())
   OR schedule_date = DATE(NOW()) AND CONVERT(schedule_time, DECIMAL) < HOUR(CURRENT_TIME())
ORDER BY schedule_date DESC, schedule_time DESC
LIMIT 1

:

SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date > DATE(NOW())
   OR schedule_date = DATE(NOW()) AND CONVERT(schedule_time, DECIMAL) > HOUR(CURRENT_TIME())
UNION ALL
(
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE schedule_date < DATE(NOW())
   OR schedule_date = DATE(NOW()) AND CONVERT(schedule_time, DECIMAL) < HOUR(CURRENT_TIME())
ORDER BY schedule_date DESC, schedule_time DESC
LIMIT 1
)
ORDER BY schedule_date ASC, schedule_time ASC

, schedule_date schedule_time DATETIME -looking, :

CONCAT(schedule_date, ' ', schedule_time, ':00')

:

SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE CONCAT(schedule_date, ' ', schedule_time, ':00') > (NOW() + INTERVAL 1 HOUR)

UNION ALL

(
SELECT schedule_date,  CONVERT(schedule_time, DECIMAL) AS schedule_time
FROM special_schedules
WHERE CONCAT(schedule_date, ' ', schedule_time, ':00') < (NOW() + INTERVAL 1 HOUR)
ORDER BY schedule_date DESC, schedule_time DESC
LIMIT 1
)

ORDER BY schedule_date ASC, schedule_time ASC
0
SELECT schedule_date, schedule_time
FROM (( SELECT schedule_date, CONVERT(schedule_time, DECIMAL) AS schedule_time
        FROM special_schedules
        WHERE schedule_date = DATE(NOW())
            AND CONVERT(schedule_time, DECIMAL) < (HOUR(CURRENT_TIME()) + 1))
        UNION
    (   SELECT schedule_date, CONVERT(schedule_time, DECIMAL)
        FROM special_schedules
        WHERE schedule_date < DATE(NOW())
        ORDER BY schedule_date DESC
        LIMIT 1)) AS h
ORDER BY schedule_date DESC

, .

0

More articles:


All Articles