Re: SQL query problem of a Quiz program

On Sat, Dec 17, 2016 at 10:25 AM, Arup Rakshit <aruprakshit1987(at)outlook(dot)com>
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 they answered. question_id 25 always
> the first questions. Any user can go though the quiz N number of time. So,
> I want to find the last occurrence of the question_id 25 for any specific
> user in the table, and select that and all answers the users gave after
> this till the end of the quiz. Any idea how to solve it in a single
> efficient query. My all try didn't work out.
>
>
>
> --------------------
> Regards,
> Arup Rakshit
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Will this work?

WITH quest AS
(SELECT id, question_id, user_id
FROM questions
WHERE user_id = 5142670086 --> substitute any user_id value
AND question_id = 25 --> substitute any question_id
value
ORDER BY 1, 2, 3)
SELECT * FROM quest
WHERE id IN (SELECT max(id) FROM quest);

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.