On Wed, 29 Apr 2015, Olivier Nicole wrote:
I have a table where each record is made of one item_number and one
data_value.
You do not have any other column ? In particular you do not have any
unique key record identifier ? All my tables have a column with a record
sequence number "seq int NOT NULL AUTO_INCREMENT" which is also a key
KEY auxiliary(seq). This is useful a posteriori to locate particular
records.
What is the command to select all the records where an item_number has
the data 1 but not the data 2?
1) by "select" you mean display at the terminal using the mysql line mode
client, or locate all affected records for further work ?
2) am I getting it correctly that you want to locate all the cases where
a given item_number (any) has JUST ONE occurrence in the table ?
In the line mode client this can be easily done with an additional table,
which can be a temporary table.
Consider e.g. the following table (it has two columns, no seq column, and
nothing else ... actually it is a table of seq pointers in two other
tables)
select * from north33w1t7_ ....
| north33 | w1t7 |
+---------+------+
| 200001 | 1 |
| 200001 | 2 |
| 200004 | 20 |
create temporary table temp1
select north33,count(*) as c from north33w1t7_
group by north33 order by north33;
temp1 will contain something like this
| north33 | c |
+---------+---+
| 200001 | 2 |
| 200004 | 1 |
so it will tell you that item 200001 has 2 counteparts, while item 200004
has 1 counterpart.
If you want to select (display) all cases in the main table with 1
counterpart do
select north33w1t7_.*
from temp1 join north33w1t7_ on
temp1.north33=north33w1t7_.north33
where c=1 :
| north33 | w1t7 |
+---------+------+
| 200004 | 20 |
| 200013 | 93 |
A different story would be if you want always to extract ONE record from
the main table, the single one if c=1, and the FIRST one if c>1.
What you define first it is up to you (the smallest data_value, the
highest data_value, a condition on other columns).
Here in general I use a trick which involves one or two temporary tables
and a variable. I initialize the variable to zero (or a value which is
not represented in the table, which shall be ordered on the columns as you
need. Then I test whether the item_number is the same as the variable, if
not I declare it to be "first", then reset the variable in the same select
statement.
set @x:=0;
select north33w1t7_.*,
if(@x<>north33w1t7_.north33,'FIRST','no') as flag,
@x:=north33w1t7_.north33
from temp1 join north33w1t7_ on temp1.north33=north33w1t7_.north33
where c>1 order by north33,w1t
| north33 | w1t7 | flag | @x:=north33w1t7_.north33 |
+---------+------+-------+--------------------------+
| 200001 | 1 | FIRST | 200001 |
| 200001 | 2 | no | 200001 |
| 200002 | 8 | FIRST | 200002 |
| 200002 | 9 | no | 200002 |
I can then save this select to a temporary table, and take my pick where
flag='FIRST'.
of course you can also do without the join with temp1 if you want
either the single or the first (i.e. c=1 and c>1)
select *,if(@x<>north33,'FIRST','no') as flag,@x:=north33 from
north33w1t7_ order by north33,w1t7
| north33 | w1t7 | flag | @x:=north33 |
+---------+------+-------+-------------+
| 200001 | 1 | FIRST | 200001 |
| 200001 | 2 | no | 200001 |
| 200002 | 8 | FIRST | 200002 |
| 200002 | 9 | no | 200002 |
| 200004 | 20 | FIRST | 200004 |
--
------------------------------------------------------------------------
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
------------------------------------------------------------------------
Do not like Firefox >=29 ? Get Pale Moon ! http://www.palemoon.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
