Problem solve
Get help with specific problems with your technologies, process and projects.
Retrieving unique values from columns in one query
I have a table where I need display only unique values from each column. For example, take this table below. How can I retrieve only one record of 'A,B,C, or D' from col_1, one record of 'Y and N', and one record of 'Y and N' for col_3 ?
my_table Col_1 Col_2 Col_3 A Y N B N Y C Y Y A N Y D N N B Y NIdeally, this is what I would like to see returned from my query.
Col_1 Col_2 Col_3 A Y Y B N N C DI have tried using the distinct function on every column (ie select distinct Col_1 Col_2 Col_3 from my_table), but needless to say it is not working for me. Can this be accomplished? Any help would be greatly appreciated!
You can't accomplish this in a single SQL statement. That is because SQL is designed to return a row as a distinct member of a result set. But in your output, you are asking for SQL to return each row as differing members of differing result sets. You'll need to accomplish this through PL/SQL or through your application programming language.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
