I have a table like this:

 ID  | Name
  1  | AA
  2  | AA
  3  | AA
  4  | BB

I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another cursor. I also need to page the results.

Expected result (Skip = 0; Take = 2)

-- p_result

 ID  | Name
  1  | AA
  2  | AA

-- p_count

How can I do this?


An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'
|improve this answer|||||

I'm not sure to understand what you really want, but i'd suggest to try the following:

SELECT   ID, Name, 'p_result' AS Type
FROM     xy


SELECT   COUNT(1), Name, 'p_count' AS Type
FROM     xy

It's untested, but it could show you sth. like this:

 ID  | Name | Type
  1  | AA   | p_result
  2  | AA   | p_result
  3  | AA   | p_result
  4  | BB   | p_result
  3  | AA   | p_count
  1  | BB   | p_count

Disadvantage of this is, that you merge all the information together. Maybe 2 queries might be more helpful in your case!

|improve this answer|||||
  • Two queries with two exact same WHERE conditions is too much risk. I someone forget to update both queries it will break return inconsistent results. I want all results (4) then return only the page 1 (row 1 and row 2), and set the count to 4 (total of rows). – BrunoLM Oct 5 '11 at 23:32

You can do it in one query:

select *, (select COUNT(*) from table where Name=p.Name) from table p
where Name='AA'
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.