Query question related to retrieving the most recent entry from a table?

Question

Hi I'm not good in framing questions. I will try my best. I'm creating a website and my question is related to the queries related to the site.This is the current query that I have.

SELECT
  GPS_modem.vehicle_no, 
  vehicle_log.longitude, 
  vehicle_log.latitude, 
  vehicle_log.timestamp
FROM
  vehicle_log,
  GPS_modem
WHERE
  GPS_modem.modem_ID = vehicle_log.modem_ID
ORDER BY 
  timestamp desc

What I want to display is the entry with the most recent timestamp from the vehicle_log table where the modem_ID from the GPS_modem table matches with the modem_ID from vehicle_log table.

I tried using DISTINCT but I didn't work. I was getting errors when I tried using MAX function. Hope you are able to understand my question, if then please help me. Thanking you in advance.

Answer 1

Depending on DB,

SELECT TOP 1 GPS_modem.vehicle_no, vehicle_log.longitude, vehicle_log.latitude,  vehicle_log.timestamp from vehicle_log,GPS_modem where GPS_modem.modem_ID = vehicle_log.modem_ID order by timestamp desc

Will work on SQL Server.

Answer 2

In MySQL you can use 'limit 1' at the end of the query.

Answer 3

This might not be the best way.

But since you order the results, you could use LIMIT.

SELECT GPS_modem.vehicle_no, vehicle_log.longitude, vehicle_log.latitude, vehicle_log.timestamp from vehicle_log,GPS_modem where GPS_modem.modem_ID = vehicle_log.modem_ID order by timestamp desc LIMIT 1

There are other solutions (probably better ones), but this is one option.

Answer 4

You can use correlated sub-query:

SELECT
  m.vehicle_no, 
  l.longitude, 
  l.latitude, 
  l.timestamp
FROM
  vehicle_log AS l,
  GPS_modem   AS m 
WHERE
  m.modem_ID = l.modem_ID
  AND l.timestamp = (
    SELECT MAX(timestamp) FROM vehicle_log WHERE modem_ID = l.modem_ID
  )

---

EDIT: In revision #1 of my answer, I had the query seen above. Then something made me think I should change it to the one below. The one below works but is unnecessary complicated, very probably performing worse. Having slept on it, I recommend against this approach. ;-)

SELECT
  m.vehicle_no,
  latest.longitude, 
  latest.latitude, 
  latest.timestamp
FROM
  GPS_modem AS m,
  (
    SELECT
      modem_ID,
      longitude, 
      latitude, 
      timestamp
    FROM
      vehicle_log AS l
    WHERE
      timestamp = (
        SELECT MAX(timestamp) FROM vehicle_log WHERE modem_ID = l.modem_ID
      )
  ) AS latest
WHERE
  m.modem_ID = latest.modem_ID

Answer 5

Hi,

Here is an example for you using SQL Server. It uses a correlated subquery with the advantage of performing a match using modem_ID rather than an equality match of timestamp.

CREATE TABLE #GPS_modem
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    vehicle_no VARCHAR(10)
)

CREATE TABLE #vehicle_log
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    modem_ID INT,
    longitude INT,
    latitude INT,
    [timestamp] DATETIME DEFAULT GETDATE()
)

INSERT INTO #GPS_modem
SELECT 'ABC123' UNION ALL SELECT 'XYZ123'

INSERT INTO #vehicle_log
SELECT 1,234,543,GETDATE()
UNION all
SELECT 2,4342,432234,GETDATE()
UNION all
SELECT 1,4322,432,DATEADD(DAY,-1,GETDATE())
UNION all
SELECT 2,6336,5324,DATEADD(DAY,-1,GETDATE())

SELECT * FROM #GPS_modem
SELECT * FROM #vehicle_log


SELECT
    vehicle_no,
    longitude,
    latitude,
    [timestamp]
FROM 
    #GPS_modem A
     inner join #vehicle_log B on
     A.ID = B.modem_ID
WHERE B.ID IN
(
    SELECT TOP 1 ID
    FROM #vehicle_log
    WHERE modem_ID = A.ID
    ORDER BY [timestamp] DESC 
)

DROP TABLE #GPS_modem
DROP TABLE #vehicle_log

Cheers, John

Answer 6

In Oracle:

SELECT
  GPS_modem.vehicle_no, 
  vehicle_log.longitude, 
  vehicle_log.latitude, 
  vehicle_log.timestamp
FROM
  (
  SELECT v.*, ROW_NUMBER() OVER (PARTITION BY modem_id ORDER BY timespace DESC) AS rn
  ) l, GPS_modem m
WHERE
  l.rn = 1
  AND m.modem_ID = l.modem_ID

In MySQL:

SELECT *
FROM (
   SELECT DISTINCT g.id AS gid, (
     SELECT l.modem_id
     FROM vehicle_log l
     WHERE l.modem_id = g.modem_id
     ORDER BY timestemp DESC
     LIMIT 1
     ) lid
 ), vehicle_log lo, GPS_modem go
WHERE lo.modem_id = lid
  AND go.modem_id = lo.modem_id