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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
