CODEWITHSUNDEEP
sqloracle-database
user1384586
user1384586

Reputation: 53

SQL query to simulate distinct

SELECT DISTINCT col1, col2 FROM table t ORDER BY col1;

This gives me distinct combination of col1 & col2. Is there an alternative way of writing the Oracle SQL query to get the unique combination of col1 & col2 records with out using the keyword distinct?

Upvotes: 3

Views: 792

Answers (7)

David Aldridge
David Aldridge

Reputation: 52386

Another ...

select   col1,
         col2
from     (
         select col1,
                col2,
                rowid,
                min(rowid) over (partition by col1, col2) min_rowid
         from   table)
where    rowid = min_rowid
order by col1;

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Variations on the UNION solution by @aF. :

INTERSECT

SELECT col1, col2 FROM tableX
INTERSECT
SELECT col1, col2 FROM tableX
ORDER BY col1;

MINUS

SELECT col1, col2 FROM tableX
MINUS
SELECT col1, col2 FROM tableX WHERE 0 = 1
ORDER BY col1;

MINUS (2nd version, it will return one row less than the other versions, if there is (NULL, NULL) group)

SELECT col1, col2 FROM tableX
MINUS
SELECT NULL, NULL FROM dual
ORDER BY col1;

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52386

Yet another ...

select
  col1,
  col2
from
  table t1
where
  not exists (select *
                from table t2
               where t2.col1  = t1.col1 and
                     t2.col2  = t1.col2 and
                     t2.rowid > t1.rowid)
order by
  col1;

Upvotes: 3

aF.
aF.

Reputation: 66707

select col1, col2
from table
group by col1, col2
order by col1

or a less elegant way:

select col1,col2 from table
UNION
select col1,col2 from table
order by col1;

or a even less elegant way:

select a.col1, a.col2
from (select col1, col2 from table
UNION
select NULL, NULL) a
where a.col1 is not null
order by a.col1

Upvotes: 3

user330315
user330315

Reputation:

Another - yet overly complex and somewhat useless - solution: 

select *
from (
   select col1, 
          col2, 
          row_number() over (partition by col1, col2 order by col1, col2) as rn
   from the_table
)
where rn = 1
order by col1

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132630

Use the UNIQUE keyword which is a synonym for DISTINCT:

SELECT UNIQUE col1, col2 FROM table t ORDER BY col1;

Upvotes: 6

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

I don't see why you would want to but you could do 

SELECT col1, col2 FROM table_t GROUP BY col1, col2 ORDER BY col1

Upvotes: 5

Related Questions