unique pair of columns
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: unique pair of columns

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    unique pair of columns

    Hi, I need to create a view for three columns a, b, c from a table. The pair of b, c needs to be unique. Is there a good way I can do this?

    Thank you very much for the help!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I can think of three different ways of interpreting your question . . . I suggest you give us an example of the data in the table and what you want to see in the view.

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    The table has three columns for example, label, sid, s_size.

    The view wants these three columns with unique combination of sid and s_size.

    Thanks!

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Originally posted by lgaorcl
    The table has three columns for example, label, sid, s_size.

    The view wants these three columns with unique combination of sid and s_size.

    Thanks!
    Why don't you just do this?

    Code:
    SELECT DISTINCT label, sid, s_size
      FROM the_table;
    Last edited by gandolf989; 02-10-2005 at 03:11 PM.
    this space intentionally left blank

  5. #5
    Join Date
    Jun 2000
    Posts
    315
    Does the statement: select distinct label, sid, s_size from the_table;
    return distinct combination of label, sid, s_size or just distinct label with sid, s_size?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Or is it
    Code:
    SELECT label, sid, s_size
      FROM the_table
    WHERE (sid, s_size) IN
    (select sid, s_size
     from the_table
     group by sid, s_size
     having count(*) = 1);
    or perhaps
    Code:
    select MAX(label), sid, s_size
     from the_table
     group by sid, s_size

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by lgaorcl
    Does the statement: select distinct label, sid, s_size from the_table;
    return distinct combination of label, sid, s_size or just distinct label with sid, s_size?
    I think you are in the perfect position to find out . . . by trying it.


    P.S. I still haven't a clue as to what you want.

  8. #8
    Join Date
    Nov 2003
    Posts
    89
    Code:
    Does the statement: select distinct label, sid, s_size from the_table;
    return distinct combination of label, sid, s_size or just distinct label with sid, s_size?
    above query return you distinct of label,sid and s_size.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by DaPi
    I think you are in the perfect position to find out . . . by trying it.
    Or are we doing your home work for you?

  10. #10
    Join Date
    Jun 2000
    Posts
    315
    DaPi:

    Thanks for the help! I tried both. The one with max(label) returned 1350 records. What does the max function do here?
    Another one returned 660 records. What's the difference between these two scripts?
    Do you have any good SQL books to recommend to me?

    Thanks!

Posting Permissions

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



Click Here to Expand Forum to Full Width