Обсуждение: [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 Sat, Dec 17, 2016 at 10:25 AM, Arup Rakshit <aruprakshit1987@outlook.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@postgresql.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);
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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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
Did you try DISTINCT ON?
id | qid | uid
----+-----+--------
1 | 25 | 1
2 | 25 | 1
3 | 25 | 1
4 | 26 | 1
5 | 26 | 1
6 | 27 | 1
7 | 27 | 1
8 | 25 | 2
9 | 25 | 2
10 | 25 | 2
11 | 26 | 2
12 | 26 | 2
13 | 27 | 2
14 | 27 | 2
15 | 25 | <NULL>
16 | 25 | <NULL>
17 | 25 | <NULL>
18 | 26 | <NULL>
19 | 26 | <NULL>
20 | 27 | <NULL>
21 | 27 | <NULL>
(21 rows)
postgres=# select distinct on (qid, uid) * from x order by uid, qid, id desc;
id | qid | uid
----+-----+--------
3 | 25 | 1
5 | 26 | 1
7 | 27 | 1
10 | 25 | 2
12 | 26 | 2
14 | 27 | 2
17 | 25 | <NULL>
19 | 26 | <NULL>
21 | 27 | <NULL>
(9 rows)
----+-----+--------
3 | 25 | 1
5 | 26 | 1
7 | 27 | 1
10 | 25 | 2
12 | 26 | 2
14 | 27 | 2
17 | 25 | <NULL>
19 | 26 | <NULL>
21 | 27 | <NULL>
(9 rows)
Is that what you need?
On Sat, Dec 17, 2016 at 4:25 PM, Arup Rakshit <aruprakshit1987@outlook.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general