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

Thank you very much.
I appreciate your help.

Ila.

-----Original Message-----
From: Chuck Mabbott [mailto:[email protected]] 
Sent: Saturday, March 25, 2006 1:35 PM
To: Ilavajuthy Palanisamy
Cc: [email protected]
Subject: Re: picking up a single record from merge table, if it has
duplicate entries

DISTINCT will pick the first item in a list of multiple types. In your 
example as shown the (1,10) would be the one I expect it to pick up.  If

there is an order that should be created in the intermediate table prior
to 
the selection.

Select *  from table
order by ID, value DESC;

Then do the DISTINCT on the results of that pass

Chuck
----- Original Message ----- 
From: "Ilavajuthy Palanisamy" <[email protected]>
To: <[email protected]>
Cc: <[email protected]>
Sent: Saturday, March 25, 2006 3:36 PM
Subject: 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



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]