CODEWITHSUNDEEP
mysqlsql
Peter Shinners
Peter Shinners

Reputation: 3776

Combine sql queries that want different filtered max values

I have two MySQL queries that are finding MAX() values from nearly identical queries. I was hoping there was a way to combine the queries for better performance.

SELECT name, MAX(version) AS highest
FROM publish
WHERE name IN ('alpha', 'beta') AND deprecated = TRUE
GROUP BY name;

SELECT name, MAX(version) AS closed
FROM publish
WHERE name IN ('alpha', 'beta') AND open = FALSE
GROUP BY name;

The schema is fairly simple. For this simple example it would look like

CREATE TABLE publish
(
    name CHAR(36) NOT NULL,
    version INT NOT NULL,
    deprecated TINYINT NOT NULL,
    open TINYINT DEFAULT 0 NOT NULL,
    PRIMARY KEY (name, version),
);
CREATE INDEX open ON publish (open);
CREATE INDEX deprecated ON publish (deprecated);

Upvotes: 0

Views: 46

Answers (3)

Kelvin Kehinde Omolumo
Kelvin Kehinde Omolumo

Reputation: 197

This should work

Use UNION (if you don't want duplicate rows) OR UNION ALL (when you want to retrieve all rows)

I will use the UNION:

(SELECT name, MAX(version) AS highest FROM publish WHERE deprecated = TRUE)
UNION
(SELECT name, MAX(version) AS closed FROM publish WHERE open = FALSE)
WHERE name IN ('alpha', 'beta') GROUP BY name;

Cheers ;)

Upvotes: 0

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

You can try UNION 

SELECT name, MAX(version) AS highest
FROM publish
WHERE name IN ('alpha', 'beta') AND deprecated = TRUE
GROUP BY name
UNION ALL
SELECT name, MAX(version) AS closed
FROM publish
WHERE name IN ('alpha', 'beta') AND open = FALSE
GROUP BY name;

But an optimized solution would be to use CASE (for conditional functionality)

SELECT name, MAX(case when deprecated = TRUE then version end) AS highest,
MAX(case when open = FALSE then version end) AS closed,
FROM publish
WHERE name IN ('alpha', 'beta')
GROUP BY name;

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

You can use conditional aggregation to combine your queries

SELECT name, MAX(case when open = FALSE then version end) AS closed,
MAX(case when deprecated = TRUE then version end) AS highest
FROM publish
WHERE name IN ('alpha', 'beta')
GROUP BY name;

Upvotes: 2

Related Questions