From: | Torsten Förtsch <tfoertsch123(at)gmail(dot)com> |
---|---|
To: | Arup Rakshit <aruprakshit1987(at)outlook(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SQL query problem of a Quiz program |
Date: | 2016-12-17 20:33:32 |
Message-ID: | CAKkG4_=Y_cSSuFsfk_mOhSgvZ1rw25fF8NPGm4dnoqciSEwyPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2016-12-17 21:34:53 | Re: Checking data checksums... |
Previous Message | Joe Conway | 2016-12-17 19:23:26 | Re: Recursive row level security policy |