Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: MySQL select to return most recent record

    Hi!

    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 Im getting, but instead of the older record I need the most recent
    (LAST datagps.Fecha / datagps.Hora).


    How can I accomplish this?

    Thanks a lot!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SenTnel
    I need same result Im getting, but instead of the older record I need the most recent
    (LAST datagps.Fecha / datagps.Hora).
    in your query, you have GROUP BY -- is this an attempt to get only one datagps row?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    64
    Looks like what you want is pretty much selecting the max rows per each group. I've came up with similar issues and have posted a blog post for it here: Microshell Optimizing SQL that selects the max/min/etc from a group.

    Hope it helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •