473,703 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Find original number of rows before applied LIMIT/OFFSET?


I need to know that original number of rows that WOULD have been returned
by a SELECT statement if the LIMIT / OFFSET where not present in the
statement.
Is there a way to get this data from PG ?

SELECT
... ;

----> returns 100,000 rows

but,

SELECT
...
LIMIT x
OFFSET y;

----> returns at most x rows

In order to build a list pager on a web site, I want to select 'pages' of a
result set at a time. However, I need to know the original select
result set
size because I still have to draw the 'page numbers' to display what
pages are
available.

I've done this TWO ways in the past:

1) TWO queries. The first query will perform a SELECT COUNT(*) ...; and
the second query performs the actualy SELECT ... LIMIT x OFFSET y;

2) Using PHP row seek and only selecting the number of rows I need.

Here is an example of method number 2 in PHP:

//----------------------------------------------------------------------
function query_assoc_pag ed ($sql, $limit=0, $offset=0) {
$this->num_rows = false;

// open a result set for this query...
$result = $this->query($sql);
if (! $result) return (false);

// save the number of rows we are working with
$this->num_rows = @pg_num_rows($r esult);

// moves the internal row pointer of the result to point to our
// desired offset. The next call to pg_fetch_assoc( ) would return
// that row.
if (! empty($offset)) {
if (! @pg_result_seek ($result, $offset)) {
return (array());
};
}

// gather the results together in an array of arrays...
$data = array();
while (($row = pg_fetch_assoc( $result)) !== false) {
$data[] = $row;

// After reading N rows from this result set, free our memory
// and return the rows we fetched...
if (! empty($limit) && count($data) >= $limit) {
pg_free_result( $result);
return ($data);
}
}

pg_free_result( $result);
return($data);
}

//----------------------------------------------------------------------

In this approach, I am 'emulating' the LIMIT / OFFSET features in PostgreSQL
by just seeking forward in the result set (offset) and only fetching the
number of rows that match my needs (LIMIT).

QUESTION: Is this the best way to do this, or is there a more efficient way
to get at the data I want? Is there a variable set in PG that tells me the
original number of rows in the query? Something like:

SELECT ORIG_RESULT_SIZ E, ...
...
LIMIT x
OFFSET y;

Or can I run another select right afterwards...li ke:

SELECT ...
...
LIMIT x
OFFSET y;

SELECT unfiltered_size _of_last_query( );

Any thoughts? Sure, the PHP function I'm using above 'works', but is it
the most efficient? I hope I'm not actually pulling all 100,000 records
across the wire when I only intend to show 10 at a time. See what I'm
getting at?

TIA,

Dante

---------
D. Dante Lorenso
da***@lorenso.c om
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #1
0 5789

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1756
by: Mike | last post by:
New to PHP and MySQL. Using PHP5 and MySQL 4.1 Windows XP Pro IIS 5.1 I'm trying to page a recordset, and am using a LIMIT clause to fetch a defined range of records from my db. However, the returned dataset is not limited to the range I have in the SQL clause. Here's the code:
3
7804
by: krystoffff | last post by:
Hi I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page, ok. But for the first page, I need to run the same query with a count(*) to know how many pages I will get (number total of rows/ X). The problem is my query is very slow (maybe 5s) because there is much
7
12086
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound to know this number after the first FETCH, isn't it? On a side note, why queries using LIMIT are SO terribly slow, compared to cursors and sometimes even ones without LIMIT? Shouldn't LIMIT be internally implemented using cursor mechanism then?...
12
3037
by: Martin Heuckeroth | last post by:
Hi Any idea on how to get a row number from the original table? We do a query and get a result. The row number from the result is different from the rownumber of the table the result originated from. I need the record number from the original tabel. How do you get THAT number? Please help, Regards, Martin
9
13782
by: campbellwarren | last post by:
Does anyone know how I could limit the number of rows allowed in a MS Access table... want to limit it to 1.
67
7695
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 4/30/06 I am looking for suggestions on how to find the date ranges where there were no transactions.
15
1785
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details (based on the primary key of the detail record). I've been trying with "TOP 3", but can't get anywhere. Using Access 2000. Something like: SELECT t1.*, TOP 3 t2.*
22
4715
by: Steve Richter | last post by:
Does the .NET framework provide a class which will find the item in the collection with a key which is closest ( greater than or equal, less than or equal ) to the keys of the collection? ex: collection keys are 20, 30, 40, 50, 60, 70, 80 find key which is >= 35. would return the 30 key.
19
4719
by: fera | last post by:
Hi to all of you guys hereÖ A friend of mine gave me: 1). A paper with a table of 350 rows x 284 columns, which each cell contains of a single number from 0 to 9. This table didnít typed yet into .xls file. It will be like table on sheet 5 of file Enigma-2.xls if it has. Since here I canít attach .xls file, I put it at Mediafire.com (a file hosting service) name Enigma-2.xls:...
0
8759
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9122
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4433
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2453
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.