SQL - Last Registration of each car

Asked

Viewed 40 times

-1

Hello.

I’m consulting a BD where it returns various data from various cars, which are collected by a hardware installed on these. The hardware sends the data every x seconds. Soon each car has several records. Needed to do a query to fetch the last record (complete, all columns) of each car. As the seat is not mine can be inserted or removed columns.

I do not have access to the registration id. Each car is identified by a 'vehicle_id' code composed of a letter and 3 numbers, e.g.: "A001". The way I have to know when the record was sent by the hardware is by a 'date' column that is stored in millisecond format, ex: 1560776363000

If I haven’t been clear please question that I try to explain otherwise.

Thanks in advance.

sql

3 answers

1

You can use the MAX aggregation function:

SELECT vehicle_id, MAX(seu_campo_data) FROM sua_tabela GROUP BY vehicle_id;

  • Hello, thank you so much for your reply. But it didn’t work out. As there are several fields and need of all n gave to use the MAX function and then *. And because the bank is not mine, can be inserted or removed columns, making it impossible to put one by one.

0

You can use the MAX aggregation function:

SELECT vehicle_id, MAX(seu_campo_data) FROM sua_tabela GROUP BY vehicle_id;

If you need more fields from your table then you can use the above query as a subquery to identify the records to be returned, all records identified by subquery value pairs.

  • It would be something like: SELECT * FROM cars WHERE date IN (SELECT MAX(date), vehicle_id FROM cars GROUP BY vehicle_id). Obs: so gave error rs.

0


Hello,

You can use subquery even though it does not have a very good performance but it will work, which database?

select * from tabela A where campo_data = (select max(campo_data) from tabela B where A.vehicle_id = B.vehicle_id)

  • I don’t know which bank. I send the query via API and receive the answer.

  • Try using a subquery then, following the example I put above..

  • It worked out this way. Thanks

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.