I added an extra code in case someone needs to use pagination with MSSQL. In my case, I have MSSQL 2008 and works pretty good.
This is an example:
$retdb = DB_SQL::select('default')->from('v_records');
$retdb->where('barcode', '=', 'T0009');
$retdb->offset(0)->limit(20); // Page 1 and 20 records
$retdb->order_by('date_created', 'DESC');
$result = $retdb->query();
This is the Query that produced.
SELECT [outer].* FROM (SELECT ROW_NUMBER() OVER(ORDER BY [date_created] DESC) as ROW_NUMBER, * FROM [v_records] WHERE [barcode] = 'T0009') AS [outer] WHERE [outer].[ROW_NUMBER] BETWEEN 1 AND 20 ORDER BY [outer].[ROW_NUMBER];
If anyone has a better idea, please share it with us.
I added an extra code in case someone needs to use pagination with MSSQL. In my case, I have MSSQL 2008 and works pretty good. This is an example:
$retdb = DB_SQL::select('default')->from('v_records'); $retdb->where('barcode', '=', 'T0009'); $retdb->offset(0)->limit(20); // Page 1 and 20 records $retdb->order_by('date_created', 'DESC'); $result = $retdb->query();
This is the Query that produced. SELECT [outer].* FROM (SELECT ROW_NUMBER() OVER(ORDER BY [date_created] DESC) as ROW_NUMBER, * FROM [v_records] WHERE [barcode] = 'T0009') AS [outer] WHERE [outer].[ROW_NUMBER] BETWEEN 1 AND 20 ORDER BY [outer].[ROW_NUMBER];
If anyone has a better idea, please share it with us.
Hope this helps!.