Home » SQL & PL/SQL » SQL & PL/SQL » distinct values in a column (Oracle 11, Solaris)
distinct values in a column [message #590481] Thu, 18 July 2013 09:17 Go to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Hello,
I have a table with two columns, like:
123   xxx
456   xxx
789   yyy
987   yyy

And in the output I would like to have:
123   xxx
789   yyy

I tried with distinct and unique on the second column, but it doesn't work.
Can you help please.
Re: distinct values in a column [message #590483 is a reply to message #590481] Thu, 18 July 2013 09:19 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Fiorst you'll need to explain the rules. i.e. why 123 xxx instead of 456 xxx?
Re: distinct values in a column [message #590484 is a reply to message #590483] Thu, 18 July 2013 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: distinct values in a column [message #590485 is a reply to message #590481] Thu, 18 July 2013 09:54 Go to previous messageGo to next message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Sorry! I just don't care for the value in first column. It can be 123, it can be 456, it doesn't matter.
Re: distinct values in a column [message #590488 is a reply to message #590485] Thu, 18 July 2013 10:04 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Then use max and group by
Re: distinct values in a column [message #590495 is a reply to message #590481] Thu, 18 July 2013 12:34 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
apenkov wrote on Thu, 18 July 2013 10:17

I tried with distinct and unique on the second column, but it doesn't work.

Explain "doesn't work."
What was your query and the results?
Re: distinct values in a column [message #590520 is a reply to message #590485] Fri, 19 July 2013 01:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1884
Registered: May 2013
Location: World Wide on the Web
Senior Member
apenkov wrote on Thu, 18 July 2013 20:24
Sorry! I just don't care for the value in first column. It can be 123, it can be 456, it doesn't matter.


If that's the case, you mean you just need any value from column1 for each group of column2. Use any of the Analytic functions like ROW_NUMBER, RANK and DENSE_RANK and just pick the 1st value(or nth value) whichever you want from each group of column2. Please post after you try.
Re: distinct values in a column [message #590522 is a reply to message #590520] Fri, 19 July 2013 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
se any of the Analytic functions like ROW_NUMBER, RANK and DENSE_RANK and just pick the 1st value(or nth value) whichever you want from each group of column2.


NO! This is not a case you use analytic functions, pablolee gave the correct answer (knowing that max could be change by any proper grouping function).

Regards
Michel
Re: distinct values in a column [message #590525 is a reply to message #590522] Fri, 19 July 2013 02:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1884
Registered: May 2013
Location: World Wide on the Web
Senior Member
Well Michel, the OP doesn't have any rule based on which he needs the output, he just wants any value from column1. So, there could be multiple ways to achieve this. Only a specific rule would direct to a specific way.
Re: distinct values in a column [message #590528 is a reply to message #590525] Fri, 19 July 2013 02:21 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
I think that the point that Michel is making is that analytics is not the best way to do this. It is akin to using a sledgehammer to crack a walnut, complete overkill.

[Updated on: Fri, 19 July 2013 02:22]

Report message to a moderator

Re: distinct values in a column [message #590543 is a reply to message #590528] Fri, 19 July 2013 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is what I meant.
When you use analytics here you have 1) apply the analytic function to all rows 2) select rows among them.
This is a case to use group functions you directly have the result.

And there are ALWAYS many ways to solve a question in SQL but many are not appropriate only few are correct.
Once again you didn't carefully read what has been posted.

Regards
Michel
Re: distinct values in a column [message #590545 is a reply to message #590543] Fri, 19 July 2013 03:30 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Fri, 19 July 2013 09:26

And there are ALWAYS many ways to solve a question in SQL but many are not appropriate only few are correct.


+1 Couldn't agree more.
Re: distinct values in a column [message #590591 is a reply to message #590481] Fri, 19 July 2013 08:24 Go to previous message
apenkov
Messages: 15
Registered: October 2012
Junior Member
Guys, I did it with MAX and GROUP BY:
select max(first_column), second_column
from my_table
group by second_column

I don't know if it is the best way, but it is working.

Thanks all for the help!
Previous Topic: How do I collapse all nulls for every table column in a SQL Statement?
Next Topic: SQL server error Occured.
Goto Forum:
  


Current Time: Fri Aug 01 05:48:25 CDT 2014

Total time taken to generate the page: 0.08898 seconds