MySQL Forums
Forum List  »  Newbie

Re: help with statement
Posted by: Felix Geerinckx
Date: July 28, 2005 09:28AM

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.

Options: ReplyQuote


Subject
Written By
Posted
July 28, 2005 09:10AM
July 28, 2005 09:26AM
Re: help with statement
July 28, 2005 09:28AM
July 28, 2005 09:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.