CODEWITHSUNDEEP
mysqlsqlunion
Dave
Dave

Reputation: 2775

Limit total number of results across tables

I want to get the most recent 100 events that happened. The events are scattered across multiple tables. Here is an example:

SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100
UNION
SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100
UNION
SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100

This will return up to 300 records. I would then take the result set, order by timestamp, and take the first 100 records. How can I perform this in a single SQL query, where SQL will only return 100 records in the result set.

I realize that it would be possible to do this by removing the LIMIT 100 from each query, and then make an outer query that adds LIMIT 100, but these tables are really big, and that's really inefficient.

Upvotes: 1

Views: 36

Answers (3)

Dave
Dave

Reputation: 2775

I think this will work

(SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100)
UNION
(SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100)
UNION
(SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100)
ORDER BY timestamp DESC LIMIT 100

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want to do this in SQL, use a subquery:

SELECT e.*
FROM ((SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100
      ) UNION ALL
      (SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100
      ) UNION ALL
      (SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100
      )
     ) e
ORDER BY timestamp DESC
LIMIT 100;

Note: Do not use UNION. It incurs overhead to remove duplicates.

Upvotes: 1

Barmar
Barmar

Reputation: 782130

Put it in a subquery, then use LIMIT 100 in the main query.

SELECT *
FROM (
    SELECT * FROM log_items_purchased ORDER BY timestamp DESC LIMIT 100
    UNION
    SELECT * FROM log_items_fulfilled ORDER BY timestamp DESC LIMIT 100
    UNION
    SELECT * FROM log_items_shipped ORDER BY timestamp DESC LIMIT 100
) AS x
ORDER BY timestamp DESC
LIMIT 100

Upvotes: 2

Related Questions