CODEWITHSUNDEEP
sqloracle-databasejoinplsql
codeObserver
codeObserver

Reputation: 6647

sql distinct on multiple cols but not all the columns

I am trying to write a merge query to read some data from table A and merge in table B.

While reading from A , I want to make sure that the rows are distinct based on few columns. so I have a query something like:

select distinct col1, col2, col3 from A. However I want the uniqueness to be on (col1,col2) pair. The above query would still get me rows which are unique for (col1,col2,col3) but not for (col1,col2)

Is there any other way to achieve this or use something like select distinct(col1,col2),col3 from A.

Also, I expect that A would have many records ~10-20 million . Moreover I am planning to iterate over multiple such table A [which itself is generated on the fly over few joins]. Hopefully the performance would be not worse of the solution, but in either case I am looking for a solution that works first and then I might run queries in part.

Oracle 10G is the version.

Thanks !

Update: I am wondering if groupby would invoke a sorting operation whcih would be very expensive than using distinct ?

Update: Answering one of the question below : With (A, B, C) and (A, B, D) rows, what is your expected result? => Assuming I want distinct on (A,B) it is fine to return either of (A,B,C) or (A,B,D) but not both.

Upvotes: 0

Views: 3630

Answers (1)

Michał Powaga
Michał Powaga

Reputation: 23183

If there more then one distinct value in col3 for specific pair (col1, col2) you'll have to choose one of them I've used here max (but you can use any of those Aggregate Functions (Oracle)):

select col1, col2, max(col3) 
from A 
group by col1, col2

Upvotes: 1

Related Questions