get max from table where sum required

Question

Suppose I have a table with following data:

gameId  difficultyLevel  numberOfQuestions
--------------------------------------------
1           1                 2
1           2                 2
1           3                 1

In this example the game is configured for 5 questions, but I'm looking for a SQL statement that will work for n number of questions.

What I need is a SQL statement that given a question, displayOrder will return the current difficulty level of question. For example - given a displayOrder of 3, with the table data above, will return 2.

Can anyone advise how the query should look like?

Answer 1

I'd recommend a game table with a 1:m relationship with a question table.

You shouldn't repeat columns in a table - it violates first normal form.

Something like this:

create table if not exists game
(   
    game_id bigint not null auto_increment,
    name varchar(64),
    description varchar(64),
    primary key (game_id)    
);

create table if not exists question
(   
    question_id bigint not null auto_increment,
    text varchar(64),
    difficulty int default 1,
    game_id bigint,
    primary key (question_id)    ,
    foreign key game_id references game(game_id)
);

select 
game.game_id, name, description, question_id, text, difficulty
game left join question
on game.game_id = question.game_id
order by question_id;

Answer 2

things might be easier for you if you change your design as duffymo suggests, but if you must do it that way, here's a query that should do the trick.

SELECT MIN(difficultyLevel) as difficltyLevel
FROM 
(
    SELECT difficltyLevel, (SELECT sum(numberOfQuestions) FROM yourtable sub WHERE sub.difficultyLevel <= yt.difficultyLevel ) AS questionTotal
    FROM yourTable yt
) AS innerSQL
WHERE innerSQL.questionTotal >= @displayOrder