Professional Web Applications Themes

creating unique rows - MySQL

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,...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: creating unique rows

    "VB.NET" <AAAAAAAAAA.ORG> wrote in message
    news:HL6dnWXzcKatvmjeRVn-jAcomcast.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 Guest

  3. #3

    Default Re: creating unique rows


    "Bill Karwin" <billkarwin.com> wrote in message
    news:dt390501oabenews3.newsguy.com...
    > "VB.NET" <AAAAAAAAAA.ORG> wrote in message
    > news:HL6dnWXzcKatvmjeRVn-jAcomcast.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 Guest

  4. #4

    Default Re: creating unique rows

    "VB.NET" <AAAAAAAAAA.ORG> wrote in message
    news:v4mdnXEMyPxgs2je4p2dnAcomcast.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.


    Bill Karwin Guest

  5. #5

    Default Re: creating unique rows

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

Similar Threads

  1. Question Remove Duplicate Rows in MySQL Table Having No Primary Key or Unique Index
    By deltaforce in forum Brainstorming Area
    Replies: 0
    Last Post: July 25th, 10:14 AM
  2. Unique Form inserting into many tables using unique id
    By Gabo Navarro in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: September 16th, 06:25 PM
  3. cloning objects and creating unique names
    By ImLearningW3D in forum Macromedia Director 3D
    Replies: 1
    Last Post: April 28th, 09:54 PM
  4. creating rows in mysql with data
    By The Voigts in forum PHP Development
    Replies: 1
    Last Post: January 29th, 01:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139