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
----- Original Message -----
From: "Søren Merser" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>; "Rhino" <[EMAIL PROTECTED]>
Sent: Sunday, March 05, 2006 11:46 AM
Subject: Re: Help on sql statement (not MySQL specifik)
Hi, I'll try
I need one record for each id in the tabel, i.e. NO duplicate id's with
TYPE set to 4 or NULL
Now, the TYPE of id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is
preserved and TYPE set to NULL while the other should be deleted
I update the tabel between queries from another table
Regards Soren
TABLE:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4 <-KEEP AS IS
3 | 2 | 6 <- DROP
4 | 3 | NULL <- KEEP AS IS
5 | 4 | NULL <-KEEP AS IS
6 | 5 | 1 <-NULL
7 | 5 | 3 <-DROP
8 | 6 | 7 <-NULL
9 | 6 | 3 <-DROP
What I neede is a SQL statement that for a given value of TYPE, lets say
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise
NULL
like:
SOLUTION:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4
3 | 3 | NULL
4 | 4 | NULL
5 | 5 | NULL
6 | 6 | NULL
I know this isn't MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list
I've reread your question and example a couple of times but I'm still not
clear on what you want but maybe this will help until you can clarify the
question.
You use WHERE to limit your result set to only specific rows of the
original table. Therefore, if you want only rows where the type is 4, you
say:
Select <column-list>
from <table-name>
where type = 4;
For example, if your table name was 'mytab' and you wanted the ID column
in the result, you'd write:
Select ID
from mytab
where type = 4;
Now, if the result showed many identical values in the columns of the
result set and you just want to know the unique values of ID that had a
type of 4, you add DISTINCT to the query:
select distinct ID
from mytab
where type = 4;
But that's where I get confused by your description of your problem.
There is only one row in your sample table that has a type of 4 so
DISTINCT isn't going to do anything for you; you'll get the same result
with or without DISTINCT, at least with the data you've shown.
I also don't understand where the nulls come in. Do you actually want to
update the data in your table permanently so that a null isn't null any
longer (or a non-null value is null)? Or do you want to _display_ a null
where something isn't null? I don't understand what your "DROP" and
"LEAVE AS IS" remarks mean.
Can you explain more fully what you are trying to do?
--
Rhino
--
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]
