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]