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?

I was trying something like this:

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

Thanks

CodePudding user response：

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;

CodePudding user response：

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.