Thread: Returning the total number of rows as a separate column when using limit
Returning the total number of rows as a separate column when using limit
From
Andreas Joseph Krogh
Date:
Hi. AFAICS the information about the *total* number of rows is in the "result" somehow. When I execute a "limit 1" query with EXPLAIN ANALYZE, I se the total number of columns in "rows=200819", so the information is there. andreak=# EXPLAIN ANALYZE select p.id from onp_crm_person p order by p.created DESC limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------Limit (cost=0.00..0.04 rows=1 width=12) (actual time=0.046..0.048 rows=1 loops=1) -> Index Scan Backward using origo_person_created_idx on onp_crm_person p (cost=0.00..8396.45 rows=200819 width=12) (actual time=0.041..0.041 rows=1 loops=1)Total runtime: 0.104 ms (3 rows) Is it possible to use some sort of "magic" function to get this number out as a separate column? And is this number accurate? It has to be the same as running a separate "count(*)"-query to count the totals, which is exactly what I'm trying to avoid. Oracle has a special rownum and over() which can be used to accomplish this: SELECT tmp.*, max(rownum) over() as total_countFROM (subquery) tmp Does PG have any equivalent way? -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+