Professional Web Applications Themes

SQL Duplicates - MySQL

Hi all, I'm facing the next problem: I've got a table like this: Name Size Total Martens 200 2 Martens 300 3 What I want is run an SQL script which will give me the following answer: Martens 200 2 Martens 200 2 Martens 300 3 Martens 300 3 Martens 300 3 It should show the data the number of times of total. Who can help? Kind Regards Sander...

  1. #1

    Default SQL Duplicates

    Hi all,

    I'm facing the next problem:

    I've got a table like this:

    Name Size Total

    Martens 200 2
    Martens 300 3

    What I want is run an SQL script which will give me the following answer:

    Martens 200 2
    Martens 200 2
    Martens 300 3
    Martens 300 3
    Martens 300 3

    It should show the data the number of times of total.

    Who can help?

    Kind Regards Sander


    Sander Martens Guest

  2. #2

    Default Re: SQL Duplicates

    Sander Martens wrote:
    > Hi all,
    >
    > I'm facing the next problem:
    >
    > I've got a table like this:
    >
    > Name Size Total
    >
    > Martens 200 2
    > Martens 300 3
    >
    > What I want is run an SQL script which will give me the following answer:
    >
    > Martens 200 2
    > Martens 200 2
    > Martens 300 3
    > Martens 300 3
    > Martens 300 3
    >
    > It should show the data the number of times of total.
    >
    > Who can help?
    >
    > Kind Regards Sander
    >
    >
    I don't think you're going to be able to do this just in MySQL. You'll
    need to do it in another programming language.



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: SQL Duplicates

    "Sander Martens" <koningsandermsn.com> wrote in message
    news:43d4d923$0$12835$ba620dc5text.nova.planet.nl ...
    > It should show the data the number of times of total.
    FWIW, I agree with Jerry, I'd handle this in application code, not in SQL.

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: SQL Duplicates

    On Mon, 23 Jan 2006 14:24:50 +0100, "Sander Martens" <koningsandermsn.com>
    wrote:
    >I've got a table like this:
    >
    >Name Size Total
    >
    >Martens 200 2
    >Martens 300 3
    >
    >What I want is run an SQL script which will give me the following answer:
    >
    >Martens 200 2
    >Martens 200 2
    >Martens 300 3
    >Martens 300 3
    >Martens 300 3
    >
    >It should show the data the number of times of total.
    mysql> create table sander (name varchar(10), size int, total int);
    Query OK, 0 rows affected (0.15 sec)

    mysql> create table rowsource (n int);
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into sander values ('Martens', 200, 2);
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into sander values ('Martens', 300, 3);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into rowsource values (1),(2),(3);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    mysql> select name, size, total
    -> from sander
    -> join rowsource
    -> on (n <= total)
    -> order by name, size;
    +---------+------+-------+
    | name | size | total |
    +---------+------+-------+
    | Martens | 200 | 2 |
    | Martens | 200 | 2 |
    | Martens | 300 | 3 |
    | Martens | 300 | 3 |
    | Martens | 300 | 3 |
    +---------+------+-------+
    5 rows in set (0.05 sec)

    --
    Andy Hassall :: [email]andyandyh.co.uk[/email] :: [url]http://www.andyh.co.uk[/url]
    [url]http://www.andyhsoftware.co.uk/space[/url] :: disk and FTP usage ysis tool
    Andy Hassall Guest

  5. #5

    Default Re: SQL Duplicates

    Thanks for the code.

    In the meantime I've managed to do it with some programming code.

    Kind Regards Sander


    "Andy Hassall" <andyandyh.co.uk> schreef in bericht
    news:cgqat1ht403cbfogj3ud74skfdvohaqm6u4ax.com...
    > On Mon, 23 Jan 2006 14:24:50 +0100, "Sander Martens"
    > <koningsandermsn.com>
    > wrote:
    >
    >>I've got a table like this:
    >>
    >>Name Size Total
    >>
    >>Martens 200 2
    >>Martens 300 3
    >>
    >>What I want is run an SQL script which will give me the following answer:
    >>
    >>Martens 200 2
    >>Martens 200 2
    >>Martens 300 3
    >>Martens 300 3
    >>Martens 300 3
    >>
    >>It should show the data the number of times of total.
    >
    > mysql> create table sander (name varchar(10), size int, total int);
    > Query OK, 0 rows affected (0.15 sec)
    >
    > mysql> create table rowsource (n int);
    > Query OK, 0 rows affected (0.01 sec)
    >
    > mysql> insert into sander values ('Martens', 200, 2);
    > Query OK, 1 row affected (0.01 sec)
    >
    > mysql> insert into sander values ('Martens', 300, 3);
    > Query OK, 1 row affected (0.00 sec)
    >
    > mysql> insert into rowsource values (1),(2),(3);
    > Query OK, 3 rows affected (0.00 sec)
    > Records: 3 Duplicates: 0 Warnings: 0
    >
    > mysql> select name, size, total
    > -> from sander
    > -> join rowsource
    > -> on (n <= total)
    > -> order by name, size;
    > +---------+------+-------+
    > | name | size | total |
    > +---------+------+-------+
    > | Martens | 200 | 2 |
    > | Martens | 200 | 2 |
    > | Martens | 300 | 3 |
    > | Martens | 300 | 3 |
    > | Martens | 300 | 3 |
    > +---------+------+-------+
    > 5 rows in set (0.05 sec)
    >
    > --
    > Andy Hassall :: [email]andyandyh.co.uk[/email] :: [url]http://www.andyh.co.uk[/url]
    > [url]http://www.andyhsoftware.co.uk/space[/url] :: disk and FTP usage ysis tool

    Sander Martens Guest

Similar Threads

  1. Finding duplicates
    By André Hänsel in forum MySQL
    Replies: 0
    Last Post: February 11th, 02:36 AM
  2. checking duplicates
    By Silverfox in forum PERL Beginners
    Replies: 1
    Last Post: December 30th, 10:29 PM
  3. deleting duplicates
    By blkli0n webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 3
    Last Post: October 11th, 08:09 AM
  4. counting duplicates
    By Dinesh.T.K in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:52 PM
  5. No Duplicates
    By Bebop & Rocksteady in forum FileMaker
    Replies: 2
    Last Post: June 28th, 11:30 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