Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Question Unanswered: How to retrieve unique rows based on column value combinations?

    I have a table mytable like below;

    Code:
    ╔═════════╦═════╦═════╗
    ║ product ║ tag ║ lot ║
    ╠═════════╬═════╬═════╣
    ║ 1111    ║ 101 ║ 2   ║ 
    ║ 1111    ║ 102 ║ 5   ║ 
    ║ 2222    ║ 103 ║ 6   ║ 
    ║ 3333    ║ 104 ║ 2   ║  
    ║ 4444    ║ 101 ║ 2   ║ 
    ║ 5555    ║ 101 ║ 2   ║ 
    ║ 5555    ║ 102 ║ 5   ║ 
    ║ 6666    ║ 102 ║ 2   ║ 
    ║ 6666    ║ 103 ║ 5   ║
    ║ 7777    ║ 101 ║ 2   ║ 
    ║ 7777    ║ 102 ║ 5   ║ 
    ║ 7777    ║ 103 ║ 6   ║ 
    ║ 8888    ║ 101 ║ 1   ║ 
    ║ 8888    ║ 102 ║ 3   ║ 
    ║ 8888    ║ 103 ║ 5   ║ 
    ║ 9999    ║ 101 ║ 6   ║ 
    ║ 9999    ║ 102 ║ 8   ║
    ╚═════════╩═════╩═════╝
    I have the input 101,102. I want the output like;

    2,5
    6,8

    which means, in the table, it will look for combinations 101,102, and returns the exact same combinations with different lot number. Along with this, I want to avoid duplicate rows. Here 1111 and 5555 has same tags with same corresponding lot numbers to tags, so I want only one row instead of 2 rows. Even though, 8888 has tags 101 and 102 with different lots, it cannot be considered for listing , since it includes tag 103 in addition. In short, I want products with exact 101, 102 combination, and I dont want products with any extra tags, and i dont want products with any missing tags.

    How can I achieve this?

    Here is the fiddle to start with http://sqlfiddle.com/#!9/ac8cc

  2. #2
    Join Date
    Jan 2016
    Posts
    2

    Question How to retrieve unique rows based on column value combinations?

    I have a table mytable like below;

    Code:
    ╔═════════╦═════╦═════╗
    ║ product ║ tag ║ lot ║
    ╠═════════╬═════╬═════╣
    ║ 1111    ║ 101 ║ 2   ║ 
    ║ 1111    ║ 102 ║ 5   ║ 
    ║ 2222    ║ 103 ║ 6   ║ 
    ║ 3333    ║ 104 ║ 2   ║  
    ║ 4444    ║ 101 ║ 2   ║ 
    ║ 5555    ║ 101 ║ 2   ║ 
    ║ 5555    ║ 102 ║ 5   ║ 
    ║ 6666    ║ 102 ║ 2   ║ 
    ║ 6666    ║ 103 ║ 5   ║
    ║ 7777    ║ 101 ║ 2   ║ 
    ║ 7777    ║ 102 ║ 5   ║ 
    ║ 7777    ║ 103 ║ 6   ║ 
    ║ 8888    ║ 101 ║ 1   ║ 
    ║ 8888    ║ 102 ║ 3   ║ 
    ║ 8888    ║ 103 ║ 5   ║ 
    ║ 9999    ║ 101 ║ 6   ║ 
    ║ 9999    ║ 102 ║ 8   ║
    ╚═════════╩═════╩═════╝
    I have the input 101,102. I want the output like;

    2,5
    6,8

    which means, in the table, it will look for combinations 101,102, and returns the exact same combinations with different lot number. Along with this, I want to avoid duplicate rows. Here 1111 and 5555 has same tags with same corresponding lot numbers to tags, so I want only one row instead of 2 rows. Even though, 8888 has tags 101 and 102 with different lots, it cannot be considered for listing , since it includes tag 103 in addition. In short, I want products with exact 101, 102 combination, and I dont want products with any extra tags, and i dont want products with any missing tags.

    How can I achieve this?

    Here is the fiddle to start with http://sqlfiddle.com/#!9/ac8cc

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what SQL have you tried thus far? Sounds like you should be joining the table to itself, selecting the distinct lot from both representations of the table. In one representation you get the 101's and in the other the 102's. Lastly, use a not exists subselect for tags other than 101 and 102.
    Dave

Tags for this Thread

Posting Permissions

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