CODEWITHSUNDEEP
mysqlsql
Pelin
Pelin

Reputation: 457

Mysql - Select the closest min value if the specified value doesn't exists

I have table containing multiple same element with different build numbers. What I am trying is if specified build doesn't exists, system will return the mimimum closest build value for each element separately. If build exists, it will simply show it.

Example table;

Name Build
App1 1
App2 2
App1 3
App1 4
App2 5
App3 10
App3 11

For instance;

Scenarios for only App1, App2

Scenario 1 : User searches for build 3 :

User searches for build 2 :

Scenario 2 : Both name doesn't have the build number user specifies, it should return the closest build of each;

User searches for build 6 :

--EDIT--

Scenario for App1, App2, App3

Scenario 3 : Some of the name has the exact or less build specified but the other name has bigger build than user search. Then it should return the lowest first of high.

User searches for build 9 :

I tried several ways of achieving this but all I got is nothing. Is it possible to achieve with single query or do I need multiple query?

Thanks in advance!

Upvotes: 0

Views: 37

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

This should be a straight forward query.

select max(Build), Name from test
where Build<= 6       
group by Name

for the 2nd scenario.

with cte as (
 select max(Build) as build, Name from test
 where Build<= 6      
 group by Name )
select * from cte
union 
select max(Build), Name from test where Name not in (select Name from cte)
group by Name

try this dbfiddle

Upvotes: 2

Related Questions