PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select only one row for a user when there are multiple rows for that user based on a condition

I have a multi column table that I have derived by joining other tables

UserId. Attempts. quizno
First 1 assd.
First 2 ffjfjf.
Second 1 cbcbc.
Second 2 tititi.
Second 3 cncncn.

I want a table that returns the row for each user with the highest attempt

UserId. Attempts. quizno
First 2 ffjfjf
Second 3 cncncn
Tags:

3

Answers


  1. 0

    Based on your comment, I guess that your table has more than 2 columns rather than just two.
    If you only have 2 columns, what @Abra put in the comment works.

    One suggestion is:

    SELECT UserId, quizno, Attempts 
FROM (
SELECT UserId, 
       ROW_NUMBER() OVER ( PARTITION BY UserId ORDER BY Attempts DESC) RowNuber,
       quizno, Attempts 
FROM TABLE
) a
WHERE a.RowNumber = 1
    Login or Signup to reply.
  2. 0

    That’s a distinct on. It selects the first/top per group: demo

    select distinct on ("UserId.") *
from user_attempts
order by "UserId.", "Attempts." desc;
    UserId. Attempts. quizno
    First 2 ffjfjf.
    Second 3 cncncn.

    You’re telling it in the parentheses which fields decide what a group is, similar to a group by, then in the order by you need to repeat that list followed by the fields which together decide which rows ends up as the "top" one.

    Login or Signup to reply.
  3. 0

    My solution.

    Creating the [database] table:

    create table UAQ (USER_ID varchar(6), ATTEMPTS int, QUIZNO varchar(7))

    I populate the table with sample data from your question.

    My query:

    select T1.USER_ID, T1.ATTEMPTS, T1.QUIZNO
  from UAQ T1
  join (select USER_ID, max(ATTEMPTS) as BIG from UAQ group by USER_ID) T2
    on (T1.USER_ID = T2.USER_ID and T1.ATTEMPTS = T2.BIG)

    Note that table T2 contains the maximum attempts per user.

    My results:

    user_id attempts quizno
    First 2 ffjfjf.
    Second 3 cncncn.

    Refer to this db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search