picking up a single record from merge table, if it has duplicate entries
------_=_NextPart_001_01C65044.F5635004
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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.
For e.g.
Table1 has
Rec1
Rec2
Rec3
Table2 has
Rec4
Rec5
Rec1
Now the data retrieved from merge table produces
Rec1
Rec2
Rec3
Rec4
Rec5
Rec1
But we need one instance of record Rec1. The selection of this record is
based on the query, some times it is based on the max bandwidth
(bandwidth is a field), some time based on distinct.
Is there a SQL query, which will resolve my requirement?
Thanks in advance.
Ila.
------_=_NextPart_001_01C65044.F5635004--
Re: picking up a single record from merge table, if it has duplicateentries
--------------080808050700080804060908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------080808050700080804060908--
RE: picking up a single record from merge table, if it has duplicate entries
------_=_NextPart_001_01C6504C.06FE885E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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=3DMERGE U
NION=3D(t1, t2) INSERT_METHOD=3DLAST;
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:rich [at] richgray.com]
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32 [at] 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
------_=_NextPart_001_01C6504C.06FE885E--
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" <ipalanisamy [at] consentry.com>
To: <rich [at] richgray.com>
Cc: <win32 [at] 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:rich [at] richgray.com]
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32 [at] 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
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32 [at] m.gmane.org
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:crmabbott [at] comcast.net]
Sent: Saturday, March 25, 2006 1:35 PM
To: Ilavajuthy Palanisamy
Cc: win32 [at] lists.mysql.com
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" <ipalanisamy [at] consentry.com>
To: <rich [at] richgray.com>
Cc: <win32 [at] 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=3DMERGE U
NION=3D(t1, t2) INSERT_METHOD=3DLAST;
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:rich [at] richgray.com]
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32 [at] 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
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org
