I have 2 tables in my Postgres database.
vehicles - veh_id PK - veh_number positions - position_id PK - vehicle_id FK - time - latitude - longitude .... few more fields
I have multiple entries in Position table for every Vehicle. I would like to get all vehicle positions but the newest ones (where time field is latest). I tried query like this:
SELECT * FROM positions GROUP BY vehicle_id ORDER BY time DESC
But there's an error:
column "positions.position_id" must appear in the GROUP BY clause or be used in an aggregate function
I tried to change it to:
SELECT * FROM positions GROUP BY vehicle_id, position_id ORDER BY time DESC
but then it doesn't group entries.
I tried to found similiar problems e.g.:
PostgreSQL - GROUP BY clause or be used in an aggregate function
GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
but I didn't really helped with my problem.
Could you help me fix my query?
Is simple if you have columns on the
SELECT those should be also on the
GROUP section unless they are wrapped with aggregated function
Also dont use
* use the column names
SELECT col1, col2, MAX(col3), COUNT(col4), AVG(col5) -- aggregated columns -- dont go in GROUP BY FROM yourTable GROUP BY col1, col2 -- all not aggregated field
Now regarding your query, looks like you want
SELECT * FROM ( SELECT * , row_number() over (partition by vehicle_id order by time desc) rn FROM positions ) t WHERE t.rn = 1;