SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2003
    Location
    Manchester, UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question No Duplicates of 2 Fields in Result?

    Hi,

    I hope someone could kindly help me out here.

    I'm having problems writing a SELECT statement that will produce a
    result that contains no duplicates of a combination of two fields.

    Example output...

    Type | ID
    -------------
    a 2
    b 2
    a 3
    b 2 <- Remove this row!
    c 1

    Here, there are two instances where a row has Type=b and ID=2. In this
    case I would like only one of these rows to be included in the result.

    Hope this is clear and thanks in advance for any suggestions!

    Dom

  2. #2
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can't help you unless I see what your SELECT query looks like and it would also help to see what your table itself looks like. If you have phpMyAdmin you can go into the db and click export. You'll get something like this:

    Code:
    #
    # Table structure for table `hon_users`
    #
    
    CREATE TABLE hon_users (
      user_id int(10) unsigned NOT NULL auto_increment,
      username varchar(20) NOT NULL default '',
      password varchar(50) NOT NULL default '',
      email varchar(255) NOT NULL default '',
      activated enum('0','1') NOT NULL default '0',
      PRIMARY KEY (user_id)
    ) TYPE=MyISAM;
    -xDev

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no duplicates of a combination of two fields:
    Code:
    select distinct Type, ID from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Sep 2003
    Location
    Manchester, UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Yes sorry I've over-simplified - I should have been more specific! Rudy unfortunately I have more fields in the SELECT statement. The DISTICT keyword takes into account all the selected fields, but I would like only the Type and ID fields to be unique.

    Structure...

    CREATE TABLE `ls_incat` (
    `cat_id` int(11) NOT NULL default '0',
    `item_id` int(11) NOT NULL default '0',
    `item_type` enum('g','j','p','v','d','t') NOT NULL default 'g'
    ) TYPE=MyISAM;

    The select statement would look something like this...

    SELECT item_type, item_id, cat_id
    FROM ls_incat
    ORDER BY item_id

    And would output the following (regardless of whether DISTICT is used)...

    item_type | item_id | cat_id
    j 1 7
    j 1 16 <- Would like removed
    j 2 7
    p 2 55 <- Would like removed
    j 3 7

    So basically I would like the DISTICT operation to ignore the cat_id column. Is there any simple way do you think?

    Thanks for your help,

    Dom

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you want to return three columns where the first two are distinct, you have to apply some algorithm to decide which value from the third column to choose

    for example,

    select item_type, item_id, min(cat_id)
    from yourtable
    group by item_type, item_id

    rudy

  6. #6
    SitePoint Member
    Join Date
    Sep 2003
    Location
    Manchester, UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thanks Rudy! Using the GROUP BY clause works perfectly.

    Dom


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
  •