Geek asks and answers

SQL query to model individual

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

It gives me a great combination of col1and col2. Is there an alternative way to write an Oracle SQL query to get a unique combination of records col1and col2without using the distinct keyword?

+3
source share
7 answers

Use the UNIQUE keyword, which is synonymous with DISTINCT:

SELECT UNIQUE col1, col2 FROM table t ORDER BY col1;
+6
source

I donโ€™t understand why you want, but you could do

SELECT col1, col2 FROM table_t GROUP BY col1, col2 ORDER BY col1
+5
source 
select col1, col2
from table
group by col1, col2
order by col1

:

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

:

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
+3

, :

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
+3

...

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;
+3

UNION @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(second version, it will return one line less than other versions if there is a group (NULL, NULL))

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

Other ...

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;
+1
source

More articles:


All Articles