Additional column SQL GROUP_BY
I have the following Postgres SQL query:
SELECT movement_id, counter, MAX(standardized_output) AS standardized_output
FROM "outputs"
WHERE "outputs"."user_id" = 1 AND "outputs"."movement_id" = 1 AND (counter in (1,3,5))
GROUP BY movement_id, counter
This creates three lines nicely for me.
However, I also want to find the corresponding column done_at
. Adding done_at
to SELECT
and GROUP_BY
ends up returning hundreds of rows; I just want three lines to return the above query with the appropriate field done_at
for the output line that was found using the function MAX
.
source to share
Use CTE with ROW_NUMBER
WITH CTE AS
(SELECT movement_id, counter, standardized_output ,done_at,
ROW_NUMBER() OVER (PARTITION BY movement_id, counter ORDER BY standardized_output DESC) as RN
FROM "outputs"
WHERE "outputs"."user_id" = 1 AND "outputs"."movement_id" = 1 AND (counter in (1,3,5))
)
SELECT * FROM CTE WHERE RN=1
source to share
If you want the done_at
value field max
not to need to be added to the sentence group by
, you need to add it using the max function below
SELECT movement_id,
counter,
MAX(standardized_output) AS standardized_output,
MAX(done_at)
FROM "outputs"
WHERE "outputs"."user_id" = 1
AND "outputs"."movement_id" = 1
AND counter in (1,3,5)
GROUP BY movement_id, counter
You can use multiple aggregate columns on the same sql. I think that done_at
is a date column and you end up with hundreds of rows because of this.
source to share
Consider using a table obtained by a subquery:
SELECT t2.movement_id, t2.counter, t2.standardized_output, t1.done_at
FROM "outputs" t1
INNER JOIN
(SELECT movement_id, counter, MAX(standardized_output) AS standardized_output
FROM "outputs"
WHERE "outputs"."user_id" = 1 AND "outputs"."movement_id" = 1
AND (counter in (1,3,5))
GROUP BY movement_id, counter) t2
ON t1.movement_id = t2.movement_id
source to share