Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
   3

How can I do this?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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.

SELECT id,
       name, 
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'
share|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
ORDER BY Name

UNION

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

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!

share|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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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