I'm glad to hear that your data isn't corrupt! That would have complicated
your life a bit, at least in the short term....
The additional information you have supplied helps me understand a bit
better but I still don't really understand enough. I'll try to ask some
specific questions that will help me understand the data and what you are
trying to do better.
1. What kind of join are you doing to combine these tables? Is it an inner
join or some kind of outer join? Are you satisfied that it is correctly
joining the tables and giving you a true picture of the different events
affecting the patients? I just want to be sure that the join is giving the
right data before we go any farther; if it isn't, we should fix the join
first.
2. Does the id belong to a specific patient? For example, does id 2 belong
to Tony Blair while id 3 belongs to Jacques Chirac? I think this must be the
case, but I want to be sure.
3. What do you mean by an 'event'? Is this a surgical procedure like "remove
appendix" or just something like emptying a bedpan? What exactly is event 4?
If it's secret, that's okay but it would help me understand the problem
better to know what event 4 and a some of the other events are. Can a
patient have more than one event 4? For example, if event 4 is "empty
patient's bedpan" that can probably happen many times but if event 4 is
"patient died", that can obviously only happen once.
4. How do you propose to determine the time difference between events when
you aren't storing the times that the events took place??? You described the
record number (recno) as a simple ascending integer earlier but now I wonder
if you mean that it is actually a timestamp or datetime value? Otherwise, I
don't see how an expression like 7 - 4 (for records 7 and 4) is going to
give you a value like 2 hours and 10 minutes.
5. What do you mean when you described eType as "nominal" and not "interval"
data?
--
Rhino
----- Original Message -----
From: "Søren Merser" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 4:37 PM
Subject: Re: Help on sql statement (not MySQL specifik)
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]
--
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]
