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