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

Postgresql – Postgres query to get one value in group by clause

uid created_date user_id
1 2023-07-05 05:50:00.000 1
2 2023-07-05 06:50:00.000 1
3 2023-07-05 06:50:00.000 1
4 2023-07-05 06:40:46.000 2
5 2023-07-05 06:57:46.000 2
6 2023-07-05 06:43:46.000 2

For example, above is the data.

I am looking for output to have something like this

uid created_date user_id reason
3 2023-07-05 06:50:00.000 1 as date is same I chose latest uid
5 2023-07-05 06:57:46.000 2 choosing the latest date
Tags:

2

Answers


  1. 0

    A CTE with a ROW_NUMBER will do the trick

    The rownumber will add a number to each row starting from 1 where for a group (PARTIION BY) .

    The ORDER BYis crutial to get the wanted result.

    CREATE TABLE test (
  "uid" INTEGER,
  "created_date" VARCHAR(23),
  "user_id" INTEGER
);

INSERT INTO test
  ("uid", "created_date", "user_id")
VALUES
  ('1', '2023-07-05 05:50:00.000', '1'),
  ('2', '2023-07-05 06:50:00.000', '1'),
  ('3', '2023-07-05 06:50:00.000', '1'),
  ('4', '2023-07-05 06:40:46.000', '2'),
  ('5', '2023-07-05 06:57:46.000', '2'),
  ('6', '2023-07-05 06:43:46.000', '2');

    CREATE TABLE

    INSERT 0 6

    WITH CTE As
( SELECT 
"uid", "created_date", "user_id"
  , ROW_NUMBER() OVER(PARTITION BY "user_id" ORDER BY "created_date" DESC, "uid" DESC) as rn
  FROM test)
SELECT
"uid", "created_date", "user_id"
FROM CTE WHERE rn = 1
    uid created_date user_id
    3 2023-07-05 06:50:00.000 1
    5 2023-07-05 06:57:46.000 2 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    For few rows per group, DISTINCT ON is simplest and fastest:

    SELECT DISTINCT ON (user_id) *
FROM   tbl
ORDER  BY user_id, created_date DESC, uid DESC;  -- your desired criteria

    Best supported with a matching index on tbl (user_id, created_date DESC, uid DESC). Depends on undisclosed data distribution and cardinalities.

    Detailed explanation (including faster alternatives for many rows per group):

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

Back To Top
Search