creating unique rows

Hi. I know how to do this in a script, but i'm trying to use DB commands to
do my data aquisition and creation. Here's what i'm trying to do. I have a
table with several columns, but there are 2 sets of coordinates that are of
interest (4 rows), say A1, B1, A2, B2. I would like to populate a new table
with pairs of coordinates, but only unique coordinates, A, B. Is it
possible to write a command, or several commands to add just these
coordinates? or add them all and remove the duplicates?

Thanks,

"VB.NET" <AAAAA@AAAAA.ORG> wrote in message
news:HL6dnWXzcKatvmjeRVn-jA@comcast.com...
> Hi. I know how to do this in a script, but i'm trying to use DB commands
> to do my data aquisition and creation. Here's what i'm trying to do. I
> have a table with several columns, but there are 2 sets of coordinates
> that are of interest (4 rows), say A1, B1, A2, B2. I would like to
> populate a new table with pairs of coordinates, but only unique
> coordinates, A, B. Is it possible to write a command, or several commands
> to add just these coordinates? or add them all and remove the duplicates?

It's a little bit difficult to visualize what you are trying to do. Could
you describe the table structures more clearly?

I guess you could use the SUBSTRING or similar function to extract "A" from
"A1" for example,
and then use SELECT DISTINCT... to reduce the results down to the unique
combinations.
Put it in an INSERT INTO `table` SELECT ... to copy it all into your new
table.

Regards,
Bill K.

"Bill Karwin" <bill@karwin.com> wrote in message
news:dt390501oab@enews3.newsguy.com...
> "VB.NET" <AAAAA@AAAAA.ORG> wrote in message
> news:HL6dnWXzcKatvmjeRVn-jA@comcast.com...
>> Hi. I know how to do this in a script, but i'm trying to use DB commands
>> to do my data aquisition and creation. Here's what i'm trying to do. I
>> have a table with several columns, but there are 2 sets of coordinates
>> that are of interest (4 rows), say A1, B1, A2, B2. I would like to
>> populate a new table with pairs of coordinates, but only unique
>> coordinates, A, B. Is it possible to write a command, or several
>> commands to add just these coordinates? or add them all and remove the
>> duplicates?
>
> It's a little bit difficult to visualize what you are trying to do. Could
> you describe the table structures more clearly?
>
> I guess you could use the SUBSTRING or similar function to extract "A"
> from "A1" for example,
> and then use SELECT DISTINCT... to reduce the results down to the unique
> combinations.
> Put it in an INSERT INTO `table` SELECT ... to copy it all into your new
> table.

sorry i typed that so fast, it was a bit cryptic.

I have a table, with a bunch of columns, including a start and an end point.
each point has a pair of coordinates, say startx, starty, endx, endy. I
created another table, which i want to populate with the coordinates from
table 1, x & y, doesn't matter if they were start of end coordinates, but i
only want unique points, either from start or end. In php, i would add the
points from start and end to an array, then do an array_unique, then load
those points, but if there was a way to do it in mysql, i want to.

I'm using mysql 5.0 if that helps.

thanks!

"VB.NET" <AAAAA@AAAAA.ORG> wrote in message
news:v4mdnXEMyPxgs2je4p2dnA@comcast.com...
> I have a table, with a bunch of columns, including a start and an end
> point. each point has a pair of coordinates, say startx, starty, endx,
> endy. I created another table, which i want to populate with the
> coordinates from table 1, x & y, doesn't matter if they were start of end
> coordinates, but i only want unique points, either from start or end.

INSERT INTO table2 (x, y)
SELECT startx, starty FROM table1
UNION
SELECT endx, endy FROM table1;

By default, UNION removes duplicates.

Regards,
Bill K.

use create table <new table name> as (select DISTINCT A1, B1, A2, B2 from <old table name>);