There is a table (History_locations)

It is necessary to display the current id_location of the machines. I.e

id_rolling_car | id_location

1 | one

2 | 2

3 | one

I think something like this:

SELECT DISTINCT id_rolling_car as id_car, (SELECT HL.id_location FROM History_locations as HL WHERE HL.id_rolling_car = id_car ORDER bY HL.date DESC LIMIT 1) as id_location FROM History_locations
  • 2
    Yeah. And what is the question? - Nick Volynkin ♦
  • @NickVolynkin Help compile Sql query - BigTows
  • How can we help without solving the problem for you? - Nick Volynkin ♦
  • @NickVolynkin I would not have turned here if I had not done anything! Do you need my work or what? - BigTows
  • SELECT id_rolling_car, max(id_location) as id_location FROM history_locations` group by id_rolling_car` - Vanya Avchyan

1 answer 1

What you have written in the question is not good, because when you try to sample also the date fields, everything will collapse. In my opinion, you need to take this into account in order not to face the same problem in the future.

As an option:

 SELECT id_rolling_car, id_location, date FROM history_locations WHERE date IN (SELECT max(date) from history_locations GROUP BY id_rolling_car) ORDER BY id_rolling_car
  • Though I don’t need a date, thank you very much;) - BigTows
  • @BigTows Please :) But after all, it might be necessary in the future. Always try to think one step further and try to anticipate the problems of the future. Good luck! - Vanya Avchyan