Hi
Thank You for all Your efforts
I'll try once again to clarify my problem
My tabel (t_temp) isn't corrupt but is the result form a join from two other
tables
1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the event.
As a patient, or id can have more than one event t_temp will end up with one
or more records accordingly
if no event has occured for id, etype=NULL
t_temp:
id | etype
-----------------
1 | NULL
2 | 4
2 | 6
3 | NULL
4 | NULL
5 | 1
5 | 3
6 | 7
6 | 3
Now I want to examine for the event of etype=4, in particular the time
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has
happend othervise NULL.
Order must be preserved.
Result: the number of records will mirror the total number of operations and
the actual record number mirrors the time of the event
id | etype
-----------------
1 | NULL
2 | 4
3 | NULL
4 | NULL
5 | NULL
6 | NULL
6 operations in all, second operation had event 4
Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not
exactly indicate time of events
2)
etype is nominal, not inteval data so You can't use < or > operator
----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: "Michael Stassen" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)
You're absolutely right that I'd need some good luck for this query to
work for every possible data value that the table could continue.
I realized the combination of 'group by id' and 'select id, type' was not
very good SQL - 'select id, <column-function>' would be a much more
standard construction to go with 'group by id' - as I developed that
query. But I was too lazy to dig through the manual to find out exactly
what MySQL would do with that query; it worked fine for the data given.
But you're right, I should have at least warned that this was dubious SQL
before posting it. The original poster could easily have though that this
was actually good SQL when it isn't.
Again, cleaning up the data (assuming it is messed up!) should be the
first priority and any query would just be a bandaid until that is done.
The query would probably be a lot easier if the data was clean to start
with.
In any case, thanks for keeping me honest.
--
Rhino
----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)
Rhino wrote:
I don't really understand _why_ you want to do this but here is a query
that gives the result you want:
select id, case type when 4 then 4 else null end as type
from Soren01
group by id;
The GROUP BY ensures that you get one row for each value of id; the case
expression in the Select says that if the value of the type is 4, leave
it alone, otherwise display null.
--
Rhino
Unfortunately, that won't work unless you are very lucky. You aren't
grouping by type, and CASE is not an aggregate function. Mysql will use
the value for type from the first row it finds for each id in the CASE
statement. The following illustrate the problem:
DROP TABLE nu;
CREATE TABLE nu (recno INT, id INT, type INT);
INSERT INTO nu VALUES
(1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);
SELECT * FROM nu;
+-------+------+------+
| recno | id | type |
+-------+------+------+
| 1 | 1 | NULL |
| 2 | 2 | 4 |
| 3 | 2 | 6 |
| 4 | 3 | 5 |
| 5 | 3 | 4 |
| 6 | 3 | 3 |
+-------+------+------+
SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
FROM nu
GROUP BY id;
+------+------+
| id | type |
+------+------+
| 1 | NULL |
| 2 | 4 |
| 3 | NULL |
+------+------+
As you can see, id=3 has a row with type=4, but it isn't found.
You could do this:
SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+------+------+
| id | type |
+------+------+
| 1 | NULL |
| 2 | 4 |
| 3 | 4 |
+------+------+
but it's hard to see how that's better than your previous, simpler
suggestion
SELECT DISTINCT id FROM nu WHERE type = 4;
Michael
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
