Your query seems relatively easy if you don't need the first test (in this case,
testID 2) explicitly printed.
mysql> SELECT * FROM tests WHERE testID<=7 AND connect>0;
+---------------------+----------+----------+
| testId | connect | result |
+---------------------+----------+----------+
| 7 | 6 | ok |
| 6 | 4 | nok |
| 4 | 3 | nok |
| 3 | 2 | nok |
+---------------------+----------+----------+
The history of testID 2 is implied by your table structure, such that you know
testID 2 passed because it is the last "connect" value.
The problem I see with this occurs when you have a "repeating" data set. A
query for your next failure (after testID 7) will also return testID 7 and its
history, as would be the case with the below data. Repairing this would require
adding an additional parameter to the WHERE statement, probably using BETWEEN.
+---------------------+----------+----------+
| testId | connect | result |
+---------------------+----------+----------+
| 1 | 0 | ok |
| 2 | 0 | nok |
| 3 | 2 | nok |
| 4 | 3 | nok |
| 5 | 0 | ok |
| 6 | 4 | nok |
| 7 | 6 | ok |
| 8 | 0 | ok |
| 9 | 0 | nok |
| 10 | 9 | nok |
| 11 | 10 | ok |
+---------------------+----------+----------+
Edward Dudlik
Becoming Digital
www.becomingdigital.com
----- Original Message -----
From: "trashMan" <trashman@stripped>
To: <mysql@stripped>
Sent: Saturday, 14 June, 2003 11:28
Subject: Impossible query??
Hi, sorry for my bad english.
I've a table where i store the result of a quality test.
If the test is passed then the result=ok and connect=0
If test fail then result=nok and, if it's the first test for the
product, connect=0
When i retest a failed product i've connect= previous testid of the
product failed
It's possible to do a query for print the history of one id??
Exemple
mysql> select * from mytable;
+---------------------+----------+----------+
| testId | connect | result |
+---------------------+----------+----------+
| 1 | 0 | ok |
| 2 | 0 | nok |
| 3 | 2 | nok |
| 4 | 3 | nok |
| 5 | 0 | ok |
| 6 | 4 | nok |
| 7 | 6 | ok |
| 8 | 0 | ok |
+---------------------+----------+----------+
I want the history of testId=7
mysql> ??????
+---------------------+----------+----------+
| testId | connect | result |
+---------------------+----------+----------+
| 7 | 6 | ok |
| 6 | 4 | nok |
| 4 | 3 | nok |
| 3 | 2 | nok |
| 2 | 0 | ok |
+---------------------+----------+----------+
Thanks a lot!
Max (trashman)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1
