sql query to incrementally modify where condition until result contains what is required

Question

I need an sql query to select some items from a table based on some condition which is based on a category field. As an example consider a list of people and I fetch the people from a particular age group from the database. I want to check if the result contains at least one result from each of a number of categories. If not I want to modify the age group by extending it and check the results again. This is repeated until I get an age group for which one result is present for each category. Right now i am doing this by analyzing the results and modifying the sql query. So a number of sql select queries are sent. What is the most efficient way of doing this? I am invoking the select queries from a java program using jdbc. I am using mysql database.

Answer 1

You require to create proceudre or function as given below, may resolve your problem easily

create procedure GetPeople
     @condition varchar(max)
    as 

    begin
    declare @affectRec int

    SET @ParmDefinition = N'@cound nvarchar(max), @cout int output';

      declare @@query nvarchar(max)
      set @query= 'select * from people where  @cond;  set @cout=@@rowcount'

      exec @query

     EXECUTE sp_executesql
@query
,@ParmDefinition
,@cond = @condition
,@count = @affectRec output
;


      if @affectRec > 0
      begin
       return;
      end 
      else 
      begin
       //update condition code 
       exec GetPeople(condition)
      end  

end

Answer 2

You can do this in a single query by not applying the filtering on the category you don't know the range of. The key is sorting by "distance" from your ideal value, or ideal range.

For example, if you know fixed values (a and b) for categories A & B, but want to find values for category C, preferring values that are closest to 'idealC', then you can do

SELECT 
   A, B, C,... FROM YorData 
WHERE
   CategoryA=a AND CategoryB=b 
ORDER BY abs(C-<idealC>)

If you only want one matching row, then add "LIMIT 1" to the query.

You can extend this to work on a range, To consider a range of values for Category C as the "ideal", but also find rows that are outside of this, you change the order by clause to

ORDER BY LEAST(C-maxC, minC-C)

(where minC/maxC define the range of values that you are most interested in.)

This will produce rows within range first, followed by rows outside of the range. As before, you can use LIMIT 1 to fetch just one row.