Hi, I'm new to mysql and I just installed 4.1.1 and ran into trouble combining a subquery and 'order by'. I guess the 3 queries below show my problem
mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com'); +---------------+----------+----------+------------+----------+ | host | facility | priority | date | time | +---------------+----------+----------+------------+----------+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:08 | | 204.90.248.18 | mail | info | 2004-03-09 | 04:15:09 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---------------+----------+----------+------------+----------+ 5 rows in set (0.01 sec) mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') order by date; Empty set (0.00 sec) mysql> SELECT host,facility,priority,date,time FROM logs WHERE host=(select ip from hostip where host='ams602.avctr.gxs.com') and facility='auth' order by date; +---------------+----------+----------+------------+----------+ | host | facility | priority | date | time | +---------------+----------+----------+------------+----------+ | 204.90.248.18 | auth | info | 2004-03-06 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 02:00:03 | | 204.90.248.18 | auth | info | 2004-03-09 | 09:17:26 | +---------------+----------+----------+------------+----------+ 3 rows in set (0.01 sec) The first query selects a few records from a syslog database where entries are stored with the IP address, but which I want to search using the hostname. The second query wants to order the output by date but to my big surprise does not give any results. When I extend the query with a select on a second field and do the 'order by date' I do get a result. Am I missing something here? Thanks, Willem
