J S wrote: | Hi, | | I need a bit of help with a mySQL query. I have a list of users in a | text file called 'users': | | u655354 | u687994 | u696974 | u728141 | .. | .. | | and I need to check the last date each user logged on to the proxy in | the last 3 months. | | In my database, there is a table for the users: | | mysql> desc user_table; | +-------+------------------+------+-----+---------+----------------+ | | Field | Type | Null | Key | Default | Extra | | +-------+------------------+------+-----+---------+----------------+ | | ID | int(10) unsigned | | MUL | NULL | auto_increment | | | uid | varchar(10) | | PRI | | | | +-------+------------------+------+-----+---------+----------------+ | 2 rows in set (0.00 sec) | | and a table (tYYMMDD) for each days log: | | mysql> desc t20041209; | +--------------+----------------------+------+-----+---------+-------+ | | Field | Type | Null | Key | Default | Extra | | +--------------+----------------------+------+-----+---------+-------+ | | uid | int(10) unsigned | | MUL | 0 | | | | time | timestamp(14) | YES | | NULL | | | | ip | int(10) unsigned | | MUL | 0 | | | | urlid | int(10) unsigned | | MUL | 0 | | | | timetaken | smallint(5) unsigned | YES | | 0 | | | | cs_size | int(10) unsigned | YES | MUL | 0 | | | | sc_size | int(10) unsigned | YES | MUL | 0 | | | | method_ID | tinyint(3) unsigned | | | 0 | | | | action_ID | tinyint(3) unsigned | | | 0 | | | | virus_ID | tinyint(3) unsigned | | | 0 | | | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | | +--------------+----------------------+------+-----+---------+-------+ | 11 rows in set (0.00 sec) | | The time column here gives the actual time the user logged on, but I | would be happy just to know the date (which I could get from the table | name if the user's uid was present in there). | | Could anyone help me to write an SQL query to do this please? | | Many thanks. Please let me know if you need anymore information.
Select u.uid, max(l.time) as lastLog ~ From user_table u join t20041209 l on u.uid = l.uid ~ Group by uid;
Thanks Mike. I need to run this query over 3 months though. Is there a quick way to write:
t20041101 union t20041102 union t20041103 union ....... t20050125 union t20050126
?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
