JOHN FARRUGIA wrote:
> I have three tables "table1", "table2" and "table3". They all have the same format as
> follows:
> create table if not exists table1 (
> id int not null auto_increment primary key,
> reference varchar(50),
> value varchar(50),
> readtime timestamp on update current_timestamp default current_timestamp,
> trigger_type varchar(20),
> data_type varchar(20)
> ) TYPE = MYISAM;
>
> What I want to do is search accross all three tables for the max(vaule) of all
> distinct(reference) where data_type="Running Count" and readtime is between 6:00:00 and
> 15:00:00.
If you mean readtimes for today (July 28):
SELECT reference, MAX(value)
FROM (SELECT * FROM table1 UNION ALL SELECT * FROM table2 UNION ALL SELECT * FROM table3)
AS alltables
WHERE
data_type="Running Count" AND
readtime BETWEEN '20050727060000' and '20050728150000'
GROUP BY reference;
P.S. You realize you are looking for the MAX of a VARCHAR?
--
felix
Please use
BBCode to format your messages in this forum.