Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] SQL query problem of a Quiz program - Mailing list pgsql-general

From Torsten Förtsch
Subject Re: [GENERAL] SQL query problem of a Quiz program
Date
Msg-id CAKkG4_=Y_cSSuFsfk_mOhSgvZ1rw25fF8NPGm4dnoqciSEwyPQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] SQL query problem of a Quiz program  (Arup Rakshit <aruprakshit1987@outlook.com>)
List pgsql-general
Tree view
Did you try DISTINCT ON?

postgres=# table x;
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)


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

pgsql-general by date:

Previous
From: Joe Conway
Date:
Subject: Re: [GENERAL] Recursive row level security policy
Next
From: David Steele
Date:
Subject: Re: [GENERAL] Checking data checksums...