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!
Printable View
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!
Why don't you just do this?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!
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 itor perhapsCode: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);
Code:select MAX(label), sid, s_size
from the_table
group by sid, s_size
I think you are in the perfect position to find out . . . by trying it.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?
P.S. I still haven't a clue as to what you want.
above query return you distinct of label,sid and s_size.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?
Or are we doing your home work for you?Quote:
Originally posted by DaPi
I think you are in the perfect position to find out . . . by trying it.
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!
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.Quote:
Originally posted by lgaorcl
Do you have any good SQL books to recommend to me?
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.
.. and your error messages are more verbose also. :)Quote:
Originally posted by DaPi
If that sounds unkind - remember I'm a lot more forgiving than an SQL interpreter.