Select row with MAX(), or choose one if no MAX() exists I have a MySQL InnoDB table with the following columns:

id: UNSIGNED bigint(20) AUTO_INCREMENT (PRIMARY KEY)
name_id: UNSIGNED bigint(20)
time_stamp: decimal(26,9)
event: enum('add', 'init', 'update', 'disconnect', 'remove', 'stopped')
value: longtext
alarm_status: enum('NO_ALARM','READ','WRITE','HIHI','HIGH','LOLO','LOW','STATE','COS','COMM','TIMEOUT','HWLIMIT','CALC','SCAN','LINK','SOFT','BAD_SUB','UDF','DISABLE','SIMM','READ_ACCESS','WRITE_ACCESS')
alarm_severity: enum('NO_ALARM', 'MINOR', 'MAJOR', 'INVALID')


Each row can be uniquely identified by either its id or the combination of its name_id, time_stamp and event.

There can be two rows with the same name_id and time_stamp if one of them has an event of 'add', 'init', 'update', 'disconnect' or 'remove' and the other one has an event of 'stopped'.

I am trying to put together the following query:

For a given time_stamp t0:

For each name_id, of the rows with an event not equal to 'stopped', find the one that has the largest time_stamp less than or equal to t0. (1)

For each name_id, of the rows with an event equal to 'stopped', find the one that has the largest time_stamp less than t0. (2)

For each name_id, return the row from (1) or (2) that has the largest time_stamp. If the time_stamps are the same, return the row from (2).

I believe the following queries work for (1) and (2), but I'm not sure how to do the last step to conditionally select from them:

(1)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp &lt;= t0
AND data.event &lt;&gt; 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event &lt;&gt; 'stopped';


(2)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp &lt; t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';


I would also be interested in any suggestions for improving the table structure.
It is possible with UNION ALL. Basically, you have three criteria, therefore you need three SELECT statements. To simplify the select statements, lets create views from your selects as follows:

CREATE VIEW data1 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp &lt;= t0
AND data.event &lt;&gt; 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event &lt;&gt; 'stopped';

CREATE VIEW data2 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp &lt; t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';


Now, the select statement will be as easy as this one:

select data2.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp = data2.time_stamp
UNION ALL
select data1.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp &gt; data2.time_stamp
UNION ALL
select data2.* 
from data1 inner join data2 
on data1.name_id = data2.name_id and data1.time_stamp &lt; data2.time_stamp


SQL Fiddle (I used INT datatype for time_stamp column in the SQL Fiddle, but it shouldn't matter, as the principle is the same for timestamp datatype)

You do not need to use VIEWs, you can embed you queries into the above statement as subqueries. I have used the views just to demonstrate the technique