CODEWITHSUNDEEP
mysqlsql
user1894647
user1894647

Reputation: 663

Select TOP 1 from the result set

I would like to retrieve the top 1 value of result set of query which is connected using Union

SELECT TOP 1 * FROM
(
    SELECT paused_time as end_time
        FROM production_time
        WHERE created_time = curdate()
    UNION 
    SELECT resumed_time as end_time
        FROM pause_timer
        WHERE created_time = curdate()
    UNION
    SELECT end_time as end_time
        FROM timer_idle
        WHERE created_time = curdate()
) as end_time
ORDER BY end_time DESC

But could not get the expected result.

Upvotes: 23

Views: 82169

Answers (2)

cottontail
cottontail

Reputation: 23291

If you don't have any other columns to select (as in the OP), then you can also select MAX() instead of ORDER BY-LIMIT 1 to select the top 1.

SELECT MAX(end_time) AS end_time 
FROM (
    SELECT paused_time as end_time FROM production_time WHERE created_time = curdate()
    UNION 
    SELECT resumed_time as end_time FROM  pause_timer WHERE created_time = curdate()
    UNION
    SELECT end_time as end_time FROM  timer_idle WHERE created_time = curdate()
) AS times

Upvotes: 0

Wibbler
Wibbler

Reputation: 1045

There is no TOP keyword in MySQL as far as I am aware. What you require is Limit:

SELECT * FROM
(
    SELECT paused_time as end_time FROM production_time WHERE created_time = curdate()
    UNION 
    SELECT resumed_time as end_time FROM  pause_timer WHERE created_time = curdate()
    UNION
    SELECT end_time as end_time FROM  timer_idle WHERE created_time = curdate()
) as end_time
ORDER BY end_time DESC 
LIMIT 1

Upvotes: 49

Related Questions