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;

For instance;

Scenarios for only App1, App2

Scenario 1 : User searches for build 3 :

• App1 should return 3 and App2 should return 2 instead of empty result or vice versa;

User searches for build 2 :

• App2 should return 2 and App1 should return 1

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 :

• App1 should return 4 and App2 should return 5

--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 :

• App1 should return 4 and App2 should return 5 and App3 should return 10

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!

CodePudding user response：

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