Selecting from two tables with inner join and limit

Question

I have two tables Service and Status. The service table only holds a name and an id

| id |  name |
|----|-------|
|  1 | Test1 |
|  2 | Test2 |

And a Status table like this

| id | status | service_id |                 timestamp |
|----|--------|------------|---------------------------|
|  1 |     OK |          1 | October, 15 2015 09:03:07 |
|  2 |     OK |          1 | October, 15 2015 09:08:07 |
|  3 |     OK |          2 | October, 15 2015 10:05:23 |
|  4 |     OK |          2 | October, 15 2015 10:15:23 |

I want to get the data like this

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  1 | Test1 |     OK | October, 15 2015 09:08:07 |
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

The latest Status with the service data. I have tried this statement

SELECT ser.id, ser.name, a.status, a.timestamp
from Service ser
  inner join (select * from status
              order by Status.timestamp
              DESC limit 1) as a
    on a.service_id = ser.id

But I only get

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

How can I change the statement to get what I want?

For testing SQL Fiddle

Answer 1

You can do this:

SELECT 
  ser.id, 
  ser.name, 
  s.status, 
  s.timestamp 
FROM Service ser 
INNER JOIN status as s ON s.service_id = ser.id
INNER JOIN
(
   SELECT
     service_id, 
     MAX(timestamp) AS MaxDate
   FROM status 
   GROUP BY service_id
) AS a  ON a.service_id = s.service_id 
       AND a.MaxDate = s.timestamp;

The join with the subquery:

SELECT
  service_id, 
  MAX(timestamp) AS MaxDate
FROM status 
GROUP BY service_id

Will eliminate all the statuses except the one with the latest date.

Answer 2

For each service, use NOT EXISTS to return status only when no later one exists:

select ser.id, ser.name, st.status, st.timestamp
from service ser
  left join status st1 on ser.id = st1.service_id
where not exists (select 1 from status st2
                  where st2.service_id = st1.service_id
                    and st2.timestamp > st1.timestamp)

Optionally doing LEFT JOIN to also return services without any status. Switch to JOIN if that's not wanted.

Answer 3

Try this...

SELECT ser.id, ser.name, a.status, a.max_timesatmp_rm
from Service ser
  inner join (select *,row_number() over (partiton by service_id  order by timestamp  desc ) as max_timesatmp_rm from status
              ) as a
    on a.service_id = ser.id
    where a.max_timesatmp_rm=1;

Answer 4

You can get that result without subquery like this

SELECT s.id, s.name, 
SUBSTRING_INDEX(GROUP_CONCAT(st.status ORDER BY st.timestamp DESC),',',1) AS status_value, 
SUBSTRING_INDEX(GROUP_CONCAT(st.timestamp ORDER BY st.timestamp DESC),',',1) AS time_stamp
FROM Service s
JOIN Status st ON(st.service_id = s.id)
GROUP BY s.id
ORDER BY s.id

The output is

id  name    status_value    time_stamp
1   Test1   OK            2015-10-15 09:08:07
2   Test2   OK            2015-10-15 10:15:23

Answer 5

You can get result by using this query

SELECT MAX(s.id),
  sr.id, 
  sr.name, 
  s.status, 
  s.timestamp 
FROM Service AS sr 
INNER JOIN status AS s ON s.service_id = sr.id
GROUP BY sr.id
ORDER BY s.id ASC

I think it will work for you