Hey all, My Question:
1. I got a table which tracks errors on systems: mysql> select id, sys_id, beginn, end, status from error_t; +----+--------+---------------------+---------------------+---------+ | id | sys_id | begin | end | state | +----+--------+---------------------+---------------------+---------+ | 1 | 1 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 2 | 2 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 3 | 3 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 4 | 4 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 5 | 1 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 6 | 2 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 7 | 3 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 8 | 4 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | | 9 | 1 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT | | 10 | 2 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT | | 11 | 3 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT | | 12 | 4 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT | +----+--------+---------------------+---------------------+---------+ BEHOBEN means SOLVED AKUT means THERE_IS_STILL_AN_ERROR >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 2. I got a table containing the systems: mysql> select * from system_t; +----+--------------------+---------+----------+--------+ | | id | name | deleted | position | group | +----+--------------------+---------+----------+--------+ | 1 | www.test.de | 0 | NULL | NULL | | 2 | partner.test.de | 0 | NULL | NULL | | 3 | app.test.de | 0 | NULL | NULL | | 4 | web.test.de | 0 | NULL | NULL | +----+--------------------+---------+----------+--------+ >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 3. I got a table defining groups for systems with a certain weight for sorting: mysql> select * from group_t; +----+----------------+-------------+ | id | description | order | +----+----------------+-------------+ | 1 | test1 | 5 | | 2 | test2 | 9 | | 5 | another_group | 90 | +----+----------------+-------------+ ---------------- Now I need the latest state. As the "id" of "error_t" is AUTO_INCREMENT, the latest state of a system is the one with the MAX(error_t.id) out of the group of similar sys_id, i.e. the line containing id = 9 AND sys_id = 1. I also want to bring the status in a certain order (-->group_t.order) . How can I tell mysql, to fetch the that row, which fits to the MAX(id) of a system? The example below has been my failed try. I want the LATEST row, i.e. something like "WHERE max_id = err.id" mysql> SELECT DISTINCT sys.name,err.state,err.end,err.begin,err.id,MAX(err.id) AS max_id FROM system_t AS sys LEFT JOIN error_t AS err ON sys.id = err.sys_id LEFT JOIN group_t AS gr ON sys.group = gr.id WHERE sys.deleted = 0 GROUP BY sys.name ORDER BY gr.order DESC, gr.order DESC, sys.name ASC; +-----------------+---------+------------+------------+------+--------+ | name | state | end | begin | id | max_id | +-----------------+---------+------------+------------+------+--------+ | app.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 | 3 | 11 | | partner.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 | 2 | 10 | | web.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 | 4 | 12 | | www.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 | 1 | 9 | +-----------------+---------+------------+------------+------+--------+ Either I'm stupid or it's impossible. Let's see which case is true. :) cheers & ThX for any help... Chris --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
