Fetching data only if there are multiple rows in the result

Question

Suppose I have a table abc with columns p_id ,u_id, and comments. I want to fetch the data from this table only if there are multiple rows for a particular p_id value (there is a single row of "junk" data in the table for every p_id that I want to ignore). How can I structure my query so that I can determine whether there are multiple rows for a p_id without fetching the data from the cursor.

Currently, my code looks something like this

Declare
  Cursor pqr is 
  Select p_id,u_id,comments from abc where p_id=123;
Begin

--I want to ignore the results if this query returns 0 or 1 row.
--I only want to process the data if this query returns multiple rows

End;

Justin Cave · Accepted Answer · 2012-01-18T18:33:49.807

2

You can add an analytic COUNT to your query so that the number of rows in the result set will be available in each row that you fetch from the cursor

Declare
  Cursor pqr is 
   select p_id,u_id,comments
     from (Select p_id,u_id,comments,count(*) over () cnt
             from abc 
            where p_id=123)
    where cnt > 1;
Begin

The probably doesn't buy you much, if any, performance, however. You're forcing Oracle to materialize the entire result set in order to count the number of rows which is likely to make the code slower. You're also adding another column to every row in the result set which will increase memory usage. And you still have to fetch the first row in order to access the CNT column.

What is the business problem you are trying to solve that requires that you know how many rows are going to be fetched before you fetch all the rows? That isn't something that is commonly needed so perhaps there is a better way to solve whatever problem you're facing.

edited Jan 18 '12 at 18:33

answered Jan 18 '12 at 18:13

Justin Cave

227,342
24
367
384

My business requirement is that ,i need to to fetch records from the table ,if the table record count is more than one i need to print all the record.Thats why i am trying to avoid fetch from cursor and at the end find the row count ...and this cursor is running for n number of companies in a script ,since this is a one time script thats why i am not worring about its performance – Gaurav Soni Jan 18 '12 at 18:17
Your analytical function is giving correct result ,Can we use ROW_NUMBER( ) and order by ROW_NUMBER( ) desc,so from the first record i get to know how many records are there – Gaurav Soni Jan 18 '12 at 18:22
@user1138658 - What happens if the query returns just 1 row? Do you do nothing? What happens if the query returns 0 rows? – Justin Cave Jan 18 '12 at 18:23
:Yes i dnt want to do anything if record count is 1,Actually there is some garbage value in table for all companies ,ideally it should come 1 only ,but if it come more than 1 ,than business will manually delete the records from the table – Gaurav Soni Jan 18 '12 at 18:28
:Is the solution edited by me gives me the correct result with first fetch only .Is this is more efficient that yours? – Gaurav Soni Jan 18 '12 at 18:31
select p_id,u_id,comments,row_number() over(order by p_id)as rec from abc order by rec desc – Gaurav Soni Jan 18 '12 at 18:35
@user1138658 - Since you want to do nothing if the query returns a single row, I modified my query to only return data if the inner query returns more than 1 row. That way, you don't have to fetch from the cursor in order to determine if there are multiple rows. If you were to use the `ROW_NUMBER` analytic function, you'd have to fetch the first two rows to determine whether the cursor returns more than one row. – Justin Cave Jan 18 '12 at 18:35
:Thanks for beautiful explanation – Gaurav Soni Jan 18 '12 at 18:41

score 1 · Answer 2 · answered Jan 18 '12 at 18:33

Try this. It makes the first column be the count, so execute it, fetch the first row. Column 1 will have the number you need.

WITH 
BASE AS
(
    SELECT SOME_STUFF
    FROM SOME_TABLE
    WHERE SOME_CONDITION
)
SELECT (SELECT COUNT(*) FROM BASE),
       BASE.*
FROM BASE

Fetching data only if there are multiple rows in the result

2 Answers2