[RESOLVED] Pagination with ORDER BY

jeepin81

I was hoping some could point me in the right direction when it comes to having pagination and order by in an SQL query. On our itemmast table I want retrieve the first 15 records and sort them by price DESC. So, ideally when paging through it would show items going from low price items to the highest. Right now, the 1st 15 results look good in regard to price DESC however if I click to the next page it's grabbing the next set of records so it's not actually giving me next result set in order of price DESC. It's just grabbing those next 15 products and sorting them price DESC

Would I need to create of view of the table where it's ordered by pricing DESC?

Here a an example of my current SQL query:

$sql = "SELECT iitem, isell, istats FROM ( 
							 SELECT iitem, isell, istats,
							 ROW_NUMBER() OVER (ORDER BY iitem) 
							 AS RowNum 
							 FROM $schema.itemmast
							 WHERE istats <> 'V'
						   AND isell > '0' 
						   AND iitem LIKE '%".db2_escape_string($searchTerm)."%' ) 
					 		 AS Data 
					 		 WHERE RowNum 
					 		 BETWEEN $start 
					 		 AND $end 
	  			 		 ORDER BY isell DESC ";

Thanks

dalecosp

Use LIMIT in SQL. Might be like this (not grokking your entire query):

$sql = "SELECT iitem, isell, istats FROM (  

                             SELECT iitem, isell, istats, 
                             ROW_NUMBER() OVER (ORDER BY iitem)  

                             AS RowNum  

                             FROM $schema.itemmast 
                             WHERE istats <> 'V' 
                             AND isell > '0'  

                             AND iitem LIKE '%".db2_escape_string($searchTerm)."%' )  

                             AS Data  

                             ORDER BY isell DESC LIMIT $start, 15";

jeepin81

Thanks Dale. For some reason I was thinking the DB2 sql query engine didn't support LIMIT.

using this query I was able to get the correct results:

SELECT iitem, isell, ico, psdesc, pkeywd FROM o.itemmast
   JOIN o.itempict ON iitem = pitem
   WHERE ico = '1'
   AND isell > '0.00'
  AND pkeywd LIKE '%bowling resin%'
  ORDER BY isell ASC 
  LIMIT 7, 15;

dalecosp

jeepin81;11059537 wrote:
Thanks Dale. For some reason I was thinking the DB2 sql query engine didn't support LIMIT.

Hah ... I got lucky! You didn't state you were using DB2, I don't think. So you must have a newer version.

LIMIT support in DB2:

Kevin Adler on S.O. wrote:
SF99702 level 9 for IBM i 7.2
SF99701 level 38 for IBM i 7.1