5 messages in com.mysql.lists.win32Re: picking up a single record from m...| From | Sent On | Attachments |
|---|---|---|
| Ilavajuthy Palanisamy | 25 Mar 2006 11:45 | |
| rich gray | 25 Mar 2006 11:50 | |
| Ilavajuthy Palanisamy | 25 Mar 2006 12:36 | |
| Chuck Mabbott | 25 Mar 2006 13:35 | |
| Ilavajuthy Palanisamy | 25 Mar 2006 15:23 |
| Subject: | Re: picking up a single record from merge table, if it has duplicate entries |
|---|---|
| From: | Chuck Mabbott (crma...@comcast.net) |
| Date: | 03/25/2006 01:35:05 PM |
| List: | com.mysql.lists.win32 |
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" <ipal...@consentry.com> To: <ri...@richgray.com> Cc: <win...@lists.mysql.com> 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:ri...@richgray.com] Sent: Saturday, March 25, 2006 11:51 AM To: Ilavajuthy Palanisamy Cc: win...@lists.mysql.com 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