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!

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.

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!

Quote:

---

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;

---

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?

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

---

Quote:

---

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.

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.

Quote:

---

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?

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!

Gandoif989 and srt:

Thanks for the help! I tried to use distinct before the three columns. The query returned 1678 records.

Thanks again!

Quote:

---

Originally posted by lgaorcl
Do you have any good SQL books to recommend to me?

---

There are plenty around - but before that (excuse me for being rude) you will have to learn to express yourself simply, logically and precisely. If you can't write down in English (or Icelandic, Swahili, Urdu, Chineese or Malay) what you want, you'll never write SQL.

Do the exercise now - write down on a piece of paper twenty typical rows from that_table, then write down the result you want, then write yourself instructions on how to get there. Then find at least three different ways of misunderstanding the instructions you gave yourself.

If that sounds unkind - remember I'm a lot more forgiving than an SQL interpreter.

Quote:

---

Originally posted by DaPi
If that sounds unkind - remember I'm a lot more forgiving than an SQL interpreter.

---

.. and your error messages are more verbose also. :)