Hi, I think that it's your GROUP BY clause that isn't correct. Try replacing "GROUP BY sys.name" with "GROUP BY error_t.sys_id" (ie, group using the same table where you applied the max() function)
I didn't tryed, hope it solves. On Thu, 2003-02-20 at 15:02, Chris Czeyka wrote: > 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 > -- Diana Soares --------------------------------------------------------------------- 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
