Geek asks and answers

How to select a record if the query returns a single row or does not select a record if the query returns more rows

In oracle SQL I need to select a row, if there is only one row, if there are more rows, it should select 0 rows.

+5
source share
7 answers

If you use PL / SQL, then selecting a column using select-intowill throw an exception too_many_rowsif more than one row is returned:

declare
  var table.column%type;
begin
  select column
  into   var
  from   table
  where  ...;
end;

If you want to do this simply using SQL, you can do something like:

select *
from
  (select s.*, count(*) over () c
   from
    (select *
     from table
     where ...
     and   rownum <= 2
    ) s
)
where c = 1

UPDATE

DazzaL , rownum <= 2 , 2 . , .

+5

, , CTE

With counter as
( select count(any_field) as cnt from your_query
)
SELECT
  your_query
WHERE exists (SELECT cnt from Counter WHERE cnt=1)

1 , 1 - http://sqlfiddle.com/#!4/84c7b/2
0 , 1 rec - http://sqlfiddle.com/#!4/95c4a/1


... :
( sqlfiddle http://sqlfiddle.com/#!4/6a2d8/117)

With results as
( select * from montly_sales_totals
),
counter as
( SELECT count(name) as cnt FROM results
)
SELECT *
FROM results
WHERE exists (SELECT cnt from Counter WHERE cnt=5)
+2
SELECT fld1, fld2
FROM (SELECT COUNT(*) over() cnt ,fld1, fld2 FROM tbl WHERE fld1 = 'key')
WHERE cnt = 1
+1

, , , 0 .

, (), , ( ), -

select *
  from table1
 where 1 = (select count(1) 
              from table1
           )

If you want to see only one row from a subset of the results from your table, I would like to do something like:

with t as ( select *
              from table1
             where [put here your condition]
)
select *
  from t
 where 1 = (select count(1) 
              from t
           )
0
source

Try the following:

SELECT f1,f2
FROM  Table
WHERE (f1 = @f1) AND (f2=@f2) AND (f3=@f3)
GROUP BY f1,f2
HAVING (COUNT(*) = 1)
0
source

Try the following:

SELECT col1, col2 FROM
(SELECT count(id) as 'cnt', col1, col2 FROM table_name WHERE col1='value') 
WHERE cnt=1;
-1
source

DECLARE COL_COUNT NUMBER;
START
COL_COUNT: = 0;
SELECT COUNT (1) IN COL_COUNT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '(table name ur)';
IF COL_COUNT = 0 THEN
EXECUTE IMMEDIATE ('select * from dual');
END IF;
END

-1
source

More articles:


All Articles