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

Postgresql – How to select a row group by a column regardless of difference in other columns

I have a table named tbl_user_complete and a query like this:

user_id bill_id complete_date
user_1 bill_1 2024-01-14
user_1 bill_2 2024-02-14
user_1 bill_3 2024-05-01
user_2 bill_4 2024-01-03 
SELECT user_id
    , bill_id
    , MAX(complete_date) AS complete_date
FROM tbl_user_complete
GROUP BY user_id;

I want to select users having max complete_date and group by user_id only, and expect to result like below:

user_id bill_id complete_date
user_1 bill_3 2024-05-01
user_2 bill_4 2024-01-03

But when I run my above query, it said the query needed to be grouped by bill_id also, which returns pretty much a copy of the original table.

How should I fix the query to get the desired result ?

Tags:

3

Answers


  1. 0

    If you want to select a whole row which has a max of something, you need to use window functions to partition and order the rows, then select the first in each partition.

    with ordered_users as (
  select
    *,
    -- This will be 1 for the user_id with the highest complete_date.
    row_number() over (
      partition by user_id
      order by complete_date desc
    ) as row_num
  from tbl_user_complete
)
select user_id, bill_id, complete_date
from ordered_users
where row_num = 1;

    Demonstration

    Login or Signup to reply.
  2. 0

    Consider self-joining unit level to aggregate level to query data based on aggregate value:

    WITH agg AS (
  SELECT user_id
      , MAX(complete_date) AS max_complete_date
  FROM tbl_user_complete
  GROUP BY user_id
)

SELECT uc.user_id
    , uc.bill_id
    , uc.complete_date
FROM tbl_user_complete uc
INNER JOIN agg
   ON uc.user_id = agg.user_id
   AND uc.complete_date = agg.max_complete_date;
    Login or Signup to reply.
  3. 0 
    SELECT MAX(complete_date) AS complete_date, user_id
FROM tbl_user_complete 
GROUP BY user_id

    enter image description here

    2024-05-01
    This will work but you won’t get the bill_id. As soon as you add "bill_id" for selection it must be part of GROUP BY. and as bill ID is unique in your example it splits to each row separate.

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

Back To Top
Search