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

Postgresql – How to select min date a not max date

Hi I’m trying to select the minimum and NOT the maximun date in a table. Instead of the maximun date I want to select the date of the fourth event. Any advice?

User Event Date
xyz 1 2021-06-06
xyz 2 2021-06-26
xyz 3 2021-07-30
xyz 4 2022-01-10
xyz 5 2022-02-14
xyz 6 2022-02-21

I was trying something like this:

select user, min(date), max(date) from table
where event <= 4
group by user

Thanks

Tags:

2

Answers


  1. 0

    You can use a sub-query for the part not affected by the WHERE.

    SELECT user, date, (SELECT MIN(date) FROM one) 
FROM one 
WHERE event=4;
    Login or Signup to reply.
  2. 0

    One alternative to your current solution uses aggregation:

    SELECT
    "user",
    MIN(date) AS min_date,
    MIN(date) FILTER (WHERE event = 4) AS fourth_date
FROM yourTable
GROUP BY "user";

    For a more general solution, we can use RANK() along with a pivot query:

    WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY "user" ORDER BY date) rnk
    FROM yourTable
)

SELECT
    "user",
    MAX(date) FILTER (WHERE rnk = 1) AS min_date,
    MAX(date) FILTER (WHERE rnk = 4) AS fourth_date
FROM cte
GROUP BY "user";

    Depending on the ties behavior you want, using DENSE_RANK() or ROW_NUMBER() instead of RANK() might make sense.

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

Back To Top
Search