Web lists-archives.com

Re: Select one valuebut not the other




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