Select only partial result but get total number of rows

Question

I got stuck on SQL subquery selection. Right now, I have a table products:

 id | name  |     description      
----+-------+----------------------
  6 | 123   | this is a           +
    |       | girl.
  7 | 124   | this is a           +
    |       | girl.
  8 | 125   | this is a           +
    |       | girl.
  9 | 126   | this is a           +
    |       | girl.
 10 | 127   | this is a           +
    |       | girl.
 11 | 127   | this is a           +
    |       | girl. Isn't this?
 12 | 345   | this is a cute slair
 13 | ggg   | this is a           +
    |       | girl
 14 | shout | this is a monster
 15 | haha  | they are cute
 16 | 123   | this is cute

What I want to do is to find ( the total number of records and the first 5 records ) which contains '1' or 'this' in either name or description columns.

What I can figure out is so ugly:

SELECT *, (select count(id)
           from (SELECT * from products
                 where description like any (array['%1%','%this%'])
                           or name like any (array['%1%','%this%'])
                ) as foo
          ) as total
from (SELECT * from products
      where description like any (array['%1%','%this%'])
                or name like any (array['%1%','%this%']))
     ) as fooo
limit 5;

simple run the first query that returns count only and without any WHERE conditions or LIMIT limitations; and second query that returns all columns but LIMIT them to 5 records and uses your WHERE conditions — Yuriy Galanter, Jul 18 '13 at 22:01
@YuriyGalanter Thanks for reply. I thought about that, but actually, there is more than 100000 records, I asked this question, and some one said I should not return them all for the reason to count them — Kuan, Jul 18 '13 at 22:09
Do you want the total number of *qualifying* records or just the total number of records in the table? I assume the former, which would mean @Yuiy's comment needs to *include* the `WHERE` clause. Otherwise, it's a valid solution, that does *not* return all records, just the count. — Erwin Brandstetter, Jul 19 '13 at 02:33

score 1 · Answer 1 · answered Jul 18 '13 at 22:05

Assuming you are using postgresql 9.0+, you can use CTE's for this. Eg.

WITH p AS (
        SELECT *
        FROM products
        WHERE description LIKE ANY (ARRAY['%1%','%this%']) OR name LIKE ANY (ARRAY['%1%','%this%'])
        )
SELECT  *,
        (select count(*) from p) as total
FROM p
ORDER BY id LIMIT 5;

Erwin Brandstetter · Accepted Answer · 2013-07-19T04:45:41.670

You can use the aggregate function count() as window function to compute the total count in the same query level:

SELECT id, name, description, count(*) OVER () AS total
FROM   products p
WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
           OR name LIKE ANY ('{%1%,%this%}'::text[])
ORDER  BY id
LIMIT  5;

Quoting the manual on window functions:

In addition to these functions, any built-in or user-defined aggregate function can be used as a window function

This works, because LIMIT is applied after window functions.

I also use an alternative syntax for array literals. One is as good as the other. This one is shorter for longer arrays. And sometimes an explicit type cast is needed. I am assuming text here.

It is simpler and a bit faster than the version with a CTE in my test.

BTW, this WHERE clause with a regular expression is shorter - but slower:

WHERE  description ~ '(1|this)'
           OR name ~ '(1|this)'

Ugly, but fast

One more test: I found the primitive version (similar to what you had already) to be even faster:

SELECT id, name, description
    , (SELECT count(*)
       FROM   products p
       WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
                  OR name LIKE ANY ('{%1%,%this%}'::text[])
      ) AS total
FROM   products p
WHERE  description LIKE ANY ('{%1%,%this%}'::text[])
           OR name LIKE ANY ('{%1%,%this%}'::text[])
ORDER  BY id
LIMIT  5;

Select only partial result but get total number of rows

2 Answers2

Ugly, but fast

Linked