RE: picking up a single record from merge table, if it has duplicate entries

Looks like distinct will not work, see my example below

mysql> select * from t1;

+----+-------+

| id | value |

+----+-------+

|  1 |    10 |

|  2 |    11 |

+----+-------+

2 rows in set (0.00 sec)

 

mysql> select * from t2;

+----+-------+

| id | value |

+----+-------+

|  1 |    20 |

|  3 |    21 |

+----+-------+

2 rows in set (0.00 sec)

 

mysql> CREATE TABLE total (id INT, value BIGINT, PRIMARY KEY(id))
ENGINE=MERGE U

NION=(t1, t2) INSERT_METHOD=LAST;

 

mysql> select * from total;

+----+-------+

| id | value |

+----+-------+

|  1 |    10 |

|  2 |    11 |

|  1 |    20 |

|  3 |    21 |

+----+-------+

4 rows in set (0.00 sec)

 

What I need is the record 1, 20.

 

If I use distinct it will pickup the first occurrence of the record.

 

mysql> select distinct id, value from total group by id;

+----+-------+

| id | value |

+----+-------+

|  1 |    10 |

|  2 |    11 |

|  3 |    21 |

+----+-------+

3 rows in set (0.00 sec)

 

Ila.

 

________________________________

From: rich gray [mailto:[email protected]] 
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: [email protected]
Subject: Re: picking up a single record from merge table, if it has
duplicate entries

 


Ilavajuthy Palanisamy wrote: 

Hi,
 
 
 
Currently we have multiple MYISAM tables, we create merge table out of
these multiple tables to retrieve data.
 
Now we have same record available in multiple tables, data retrieved
from merge table produces duplicate records.
 
But what we need is to get only one record out of these multiple records
based on some condition.
 
  

[chop]
Does select distinct... not work in your example?
Rich