Обсуждение: [GENERAL] SQL query problem of a Quiz program

Hi,

Here is a sample data from table "quiz_results":

id | question_id |  user_id
----+-------------+------------
  2 |          25 | 5142670086
  3 |          26 |
  4 |          26 |
  5 |          27 |
  6 |          25 | 5142670086
  7 |          25 | 5142670086
  8 |          25 | 5142670086
  9 |          26 |
 10 |         40 | 5142670086
 11 |          29 | 5142670086


As you see above question id 25 appeared more than once. This is basically a quiz result table where for users as they
answered.question_id 25 always the first questions. Any user can go though the quiz N number of time. So, I want to
findthe last occurrence of the question_id 25 for any specific user in the table, and select that and all answers the
usersgave after this till the end of the quiz. Any idea how to solve it in a single efficient query. My all try didn't
workout.  



--------------------
Regards,
Arup Rakshit

On 12/17/2016 07:25 AM, Arup Rakshit wrote:
> Hi,
>
> Here is a sample data from table "quiz_results":
>
> id | question_id |  user_id
> ----+-------------+------------
>   2 |          25 | 5142670086
>   3 |          26 |
>   4 |          26 |
>   5 |          27 |
>   6 |          25 | 5142670086
>   7 |          25 | 5142670086
>   8 |          25 | 5142670086
>   9 |          26 |
>  10 |         40 | 5142670086
>  11 |          29 | 5142670086
>
>
> As you see above question id 25 appeared more than once. This is basically a quiz result table where for users as
theyanswered. question_id 25 always the first questions. Any user can go though the quiz N number of time. So, I want
tofind the last occurrence of the question_id 25 for any specific user in the table, and select that and all answers
theusers gave after this till the end of the quiz. Any idea how to solve it in a single efficient query. My all try
didn'twork out. 

test=# create table quiz(id int, question_id int, user_id bigint);
CREATE TABLE

est=# select * from quiz order by id, question_id;


  id | question_id |  user_id
----+-------------+------------
   2 |          25 | 5142670086
   3 |          26 |       NULL
   4 |          26 |       NULL
   4 |          26 |       NULL
   5 |          27 |       NULL
   6 |          25 | 5142670086
   7 |          25 | 5142670086
   8 |          25 | 5142670086
  10 |          40 | 5142670086
  11 |          29 | 5142670086
(10 rows)


test=# select
     *
from
     quiz
where
     user_id = 5142670086
and
     id >=
         (select
             max(id)
         from
             quiz
         where
             user_id = 5142670086
         and
             question_id = 25)
order by
     question_id;

  id | question_id |  user_id
----+-------------+------------
   8 |          25 | 5142670086
  11 |          29 | 5142670086
  10 |          40 | 5142670086
(3 rows)


>
>
>
> --------------------
> Regards,
> Arup Rakshit
>


--
Adrian Klaver
adrian.klaver@aklaver.com