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,

=20

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.

=20

For e.g.

Table1 has

Rec1

Rec2

Rec3

=20

Table2 has

Rec4

Rec5

Rec1

=20

Now the data retrieved from merge table produces

Rec1

Rec2

Rec3

Rec4

Rec5

Rec1

=20

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.

=20

Is there a SQL query, which will resolve my requirement?

=20

Thanks in advance.

=20

Ila.

=20


------_=_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)

=20

mysql> select * from t2;

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

| id | value |

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

| 1 | 20 |

| 3 | 21 |

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

2 rows in set (0.00 sec)

=20

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

NION=3D(t1, t2) INSERT_METHOD=3DLAST;

=20

mysql> select * from total;

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

| id | value |

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

| 1 | 10 |

| 2 | 11 |

| 1 | 20 |

| 3 | 21 |

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

4 rows in set (0.00 sec)

=20

What I need is the record 1, 20.

=20

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

=20

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)

=20

Ila.

=20

________________________________

From: rich gray [mailto:rich@richgray.com]=20
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32@lists.mysql.com
Subject: Re: picking up a single record from merge table, if it has
duplicate entries

=20


Ilavajuthy Palanisamy wrote:=20

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

[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"
To:
Cc:
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@richgray.com]
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32@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@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@comcast.net]=20
Sent: Saturday, March 25, 2006 1:35 PM
To: Ilavajuthy Palanisamy
Cc: win32@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=20
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=20
the selection.

Select * from table
order by ID, value DESC;

Then do the DISTINCT on the results of that pass

Chuck
----- Original Message -----=20
From: "Ilavajuthy Palanisamy"
To:
Cc:
Sent: Saturday, March 25, 2006 3:36 PM
Subject: RE: picking up a single record from merge table, if it has=20
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@richgray.com]
Sent: Saturday, March 25, 2006 11:51 AM
To: Ilavajuthy Palanisamy
Cc: win32@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@m.gmane.org