0

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

or

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?

Community
  • 1
  • 1
user3626048
  • 706
  • 4
  • 19
  • 52

2 Answers2

1

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;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

try to use this group by clause GROUP BY position_id,vehicle_id primary key then FK

kinan Dwair
  • 1
  • 1