Re: [GENERAL] SQL query problem of a Quiz program

[Torsten Förtsch]

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 | 
16 |  25 | 
17 |  25 | 
18 |  26 | 
19 |  26 | 
20 |  27 | 
21 |  27 | 
(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 | 
19 |  26 | 
21 |  27 | 
(9 rows)


Is that what you need?


On Sat, Dec 17, 2016 at 4:25 PM, 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 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
>

Re: [GENERAL] SQL query problem of a Quiz program

[Adrian Klaver]

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 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.


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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SQL query problem of a Quiz program

[Melvin Davidson]

On Sat, Dec 17, 2016 at 10: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 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);

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

Re: [GENERAL] SQL query problem

[Karsten Hilbert]

 fine). PG returns: ERROR:  Relation _con does not exist
 
 This is my query:
 
 SELECT
   _CON.con_id,
Please make sure you get the quoting right regarding table
names. PostgreSQL will fold _CON into _con unless quoted
_CON. So, it may be that you created the table with quotes
(_CON). Now, in your query you don't use quotes and thusly
it is looking for a _con table. The simple rule of thumb is to
either always or never use quotes.

HTH,
Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match