MySQL to return only last date / time record

Question

We have a database that stores vehicle's gps position, date, time, vehicle identification, lat, long, speed, etc., every minute.

The following select pulls each vehicle position and info, but the problem is that returns the first record, and I need the last record (current position), based on date (datagps.Fecha) and time (datagps.Hora). This is the select:

SELECT configgps.Fichagps, 
       datacar.Ficha, 
       groups.Nombre, 
       datagps.Hora, 
       datagps.Fecha, 
       datagps.Velocidad, 
       datagps.Status, 
       datagps.Calleune, 
       datagps.Calletowo, 
       datagps.Temp, 
       datagps.Longitud, 
       datagps.Latitud, 
       datagps.Evento, 
       datagps.Direccion, 
       datagps.Provincia 
  FROM asigvehiculos 
  INNER JOIN datacar ON (asigvehiculos.Iddatacar = datacar.Id) 
  INNER JOIN configgps ON (datacar.Configgps = configgps.Id) 
  INNER JOIN clientdata ON (asigvehiculos.Idgroup = clientdata.group) 
  INNER JOIN groups ON (clientdata.group = groups.Id) 
  INNER JOIN datagps ON (configgps.Fichagps = datagps.Fichagps) 
  Group by Fichagps;

I need same result I'm getting, but instead of the older record I need the most recent (LAST datagps.Fecha / datagps.Hora).

How can I accomplish this?

Answer 1

Add ORDER BY datagps.Fecha DESC, datagps.Hora DESC LIMIT 1 to your query.

Answer 2

Here is a screenshot of the query results, wich are ok, except for the fact that brings the first (oldest) records, instead of the newest:

Check this image: http://img7.imageshack.us/i/samplecx.jpg/

As you can see I have records from 2006, what I need are today's position, current date and time.

Thanks again!

Answer 3

I'm not sure why you are having any problems with this as Lex's answers seem good.

I would start putting ORDER BY's in your query so it puts them in an order, when it's showing the record you want as the first one in the list, then add the LIMIT.

If you want the most recent, then the following should be good enough:

ORDER BY datagps.Fecha DESC, datagps.Hora DESC

If you simply want the record that was added to the database most recently (irregardless of the date/time fields), then you could (assuming you have an auto-incremental primary key in the datagps table (I assume it's called dataID for this example)):

ORDER BY datagps.dataID DESC

If these aren't showing the data you want - then there is something missing from your example (maybe data-types aren't DATETIME fields? - if not - then maybe a CONVERT to change them from their current type before ORDERing BY would be a good idea)

EDIT: I've seen the screenshot and I'm confused as to what the issue is still. That appears to be showing everything in order. Are you implying that there are many more than 5 records? How many are you expecting?

Answer 4

Do you mean: for each record returned, you want the one row from the table datagps with the latest date and time attached to the result? If so, how about this:

# To show how the query will be executed
# comment to return actual results
EXPLAIN

SELECT
configgps.Fichagps, datacar.Ficha, groups.Nombre, datagps.Hora, datagps.Fecha,
datagps.Velocidad, datagps.Status, datagps.Calleune, datagps.Calletowo,
datagps.Temp, datagps.Longitud, datagps.Latitud, datagps.Evento,
datagps.Direccion, datagps.Provincia

FROM asigvehiculos
INNER JOIN datacar ON (asigvehiculos.Iddatacar = datacar.Id)
INNER JOIN configgps ON (datacar.Configgps = configgps.Id)
INNER JOIN clientdata ON (asigvehiculos.Idgroup = clientdata.group)
INNER JOIN groups ON (clientdata.group = groups.Id)
INNER JOIN datagps ON (configgps.Fichagps = datagps.Fichagps)

########### Add this section
LEFT JOIN datagps b ON (
    configgps.Fichagps = b.Fichagps

    # wrong condition
    #AND datagps.Hora < b.Hora
    #AND datagps.Fecha < b.Fecha)

    # might prevent indexes to be used
    AND (datagps.Fecha < b.Fecha OR (datagps.Fecha = b.Fecha AND datagps.Hora < b.Hora))

WHERE b.Fichagps IS NULL
########### 

Group by configgps.Fichagps;

Similar question here only that that one uses outer joins.

Edit (again): The conditions are wrong so corrected it. Can you show us the output of the above EXPLAIN query so we can pinpoint where the bottle neck is?

As hurikhan77 said, it will be better if you could convert both of the the columns into a single datetime field - though I'm guessing this would not be possible for your case (since your database is already being used?)

Though if you can convert it, the condition (on the join) would become:

    AND datagps.FechaHora < b.FechaHora

After that, add an index for datagps.FechaHora and the query would be fast(er).

Answer 5

What you probably want is getting the maximum of (Fecha,Hora) per grouped dataset? This is a little complicated to accomplish with your column types. You should combine Fecha and Hora into one column of type DATETIME. Then it's easy to just SELECT MAX(FechaHora) ... GROUP BY Fichagps.

It could have helped if you posted your table structure to understand the problem.