distinct SQL - return single rows

Question

I have a table with the below records :

Column A   Column B

1           XX
2           XX
3           XX
4           XX

How can I display 1 record by only using ColumnB but not using ColumnA. Like I should say

select from Table T where ColumnB ='XX'

and only 1 row should return.

Please use line breaks to make your sample data more readable. currently it looks like it is only onw row, which I guess is not what you want. — Jan, Oct 20 '18 at 07:10
Possible duplicate of [Oracle SELECT TOP 10 records](https://stackoverflow.com/questions/2498035/oracle-select-top-10-records) — Jan, Oct 20 '18 at 07:10

Barbaros Özhan · Answer 1 · 2018-10-20T12:37:43.310

You may use one of the following :

with t(colA,ColB) as
(  
 select 1,'XX' from dual union all
 select 2,'XX' from dual union all
 select 3,'XX' from dual union all
 select 4,'XX' from dual    
)
select ColB 
  from t
 where ColumnB = 'XX' and rownum = 1;

or

select ColB from
(
    with t(colA,ColB) as
    (  
     select 1,'XX' from dual union all
     select 2,'XX' from dual union all
     select 3,'XX' from dual union all
     select 4,'XX' from dual    
    )
    select ColB,
           row_number() over (order by ColB) as rn
      from t
)
where ColumnB = 'XX' and rn=1;

or if your DB version is 12c, this one works also :

with t(colA,ColB) as
(  
 select 1,'XX' from dual union all
 select 2,'XX' from dual union all
 select 3,'XX' from dual union all
 select 4,'XX' from dual    
)
select ColB 
from t
where ColumnB ='XX'
fetch {first|next} 1 {row|rows} only;

one of the keywords first or next and row or rows should be preferred.

None of these queries will return the correct result if you change the first row's `COLB` value to, say, `AAA`. Presumably, there are other values in the table, regarding condition the OP mentioned (`ColumnB ='XX'`) which you never used. — Littlefoot, Oct 20 '18 at 08:18

score 1 · Answer 2 · answered Oct 20 '18 at 08:33

If you really don't care which one of the matching records is returned just use the rownum pseudo-column:

select * from Table T where ColumnB ='XX' 
and rownum = 1;

This query simply returns the first row from the result set. It is the cheapest way to get one row. The result is non-deterministic because there is no sort order, and there can't be: rownum doesn't play nice with ORDER BY which is why it's important that you don't care which row comes back.

Shuddh · Answer 3 · 2018-10-20T08:09:31.000

0

You can do it like this

select from Table T where ColumnB ='XX' group by ColunmB

edited Oct 20 '18 at 08:09

answered Oct 20 '18 at 07:51

Shuddh

1,920
2
19
30

Sorry Didn't see the oracle tag! – Shuddh Oct 20 '18 at 08:09

score 0 · Answer 4 · answered Oct 20 '18 at 08:12

As you don't really care which a to return, how about

SQL> with test (a, b) as
  2  (select 1, 'xx' from dual union all
  3   select 2, 'xx' from dual union all
  4   select 3, 'yy' from dual
  5  )
  6  select min(a) a, min(b) b
  7  from test
  8  where b = 'xx';

         A B
---------- --
         1 xx

SQL>

score 0 · Answer 5 · answered Oct 28 '18 at 23:13

Thanks Everyone for the ideas.

I tried on of the below and it worked for me,

select a.column_name FROM tale_name a WHERE ROWID IN ( select RID FROM ( SELECT ROWID RID, ROW_NUMBER() OVER (PARTITION BY a.column_name ORDER BY ROWID) RN FROM table_name a where column_name in (Select TO_CHAR(column_name) from diff_table_name) ) WHERE RN = 1

distinct SQL - return single rows

5 Answers5