CODEWITHSUNDEEP
mysqlsql
Toleo
Toleo

Reputation: 774

a Query contains MAX() and MIN() with their own conditions

In my query here

$Query = "SELECT id, name FROM names WHERE age = 18 LIMIT 1";

I want to add MIN() and MAX() with a condition as if i made another query like this

$MQuery = "SELECT MAX/MIN(age) FROM names LIMIT 1";

My solution was merging the three queries together like this

$Query = "SELECT id, name, 
(SELECT MAX(age) FROM names WHERE  age = 18 AND age > 18 LIMIT 1) as MAX, 
(SELECT MIN(age) FROM names WHERE  age = 18 AND age < 18 LIMIT 1) as MIN
FROM names WHERE age = 18 LIMIT 1";

But i wonder if there is a more efficient way and quicker to write it, Like

$Query = "SELECT id, name, 
(MAX(age) WHERE age > 18) as MAX, 
(MIN(age) WHERE age < 18) as MIN 
FROM names WHERE age = 18 LIMIT 1";

also, Is there any idea of using LIMIT 1 or DISTINCT with MAX/MIN()-only queries?

What i expect is that if there is age 19 or age 17 to catch it using MIN and MAX if they exist

[id - name - max -min
[1 - Mario - 19 - 17]

table would be like

[id - name - age]
[1  - mario - 18]
[2  - arizo - 18]
[3  - bruno - 17]
[4  - arizo - 19]
[5  - mario - 18]

Upvotes: 0

Views: 52

Answers (1)

Vash
Vash

Reputation: 1787

MAX(CASE WHEN..) could be your way out. Also I changed the equality signs(if the age is greater than 18, you want the minimum of age in [19 to whatever] and for ages less than 18, you want max of age in [1 to 17]. Also I don't see the need for LIMIT 1. 

SELECT id, name, 
MAX(CASE WHEN age<18 THEN age END) as Max_age,
MIN(CASE WHEN age>18 THEN age END) as Min_age
FROM names
GROUP BY id, name;

Note: The next/previous age w.r.t. 18 will only apply to a given id in consideration i.e. if that particular id has more than one entry with different ages.

Upvotes: 2

Related Questions