EllisLab text mark
Advanced Search
     
MSSQL and OFFSET…
Posted: 06 July 2007 06:09 PM
Avatar
Joined: 2007-02-07
38 posts

Now, I have found this SQL statement to mimic OFFSETS in MSSQL (for pagination):


“SELECT * FROM (
  SELECT TOP {$this->db->escape($per_page)} * FROM (
    SELECT TOP {$this->db->escape($per_page+$offset)} *
    FROM nfr_vendors
    $where
    ORDER BY $order ASC) AS t1
  ORDER BY $order DESC) AS t2
ORDER BY $order ASC”

As you can see slightly modified for my own uses… It works just fine, but, has a somewhat confusing side-effect…

When I paginate results that are not in EXACT multiples of whatever my item limit is - I get ‘spill-over’; say I have 14 rows in my DB and I have an item per-page limit of 10. Normally the first page would display the first 10 rows (which this does too), then the second page would only have the REMAINING 4 rows. What actually happens is, the first page predictably displays the first 10 rows; BUT the second page displays 10 rows with the first four being the cut-off and the last four on the tail end of the results display.


First page
—————
entry1
entry2
entry3
entry4
entry5
entry6
entry7
entry8
entry9
entry10

Second page
—————-
entry5
entry6
entry7
entry8
entry9
entry10
entry11 <—- Here are the straggling results…
entry12
entry13
entry14

While it technically does work, it isn’t working exactly as I want it to. I would rather have the offset leave only those remaining rows in the results.

I am not an SQL guru but I will continue to tinker - if someone has run into this or has a moment of inspiration, the advise would be appreciated!

 
Posted: 08 July 2007 04:20 AM   [ # 1 ]   [ Rating: 0 ]
Joined: 2007-03-01
1 posts

MS SQL is not very good for pagination. Do you have to use it?
If you do, is it MS SQL 2000? MS SQL 2005 have better support for pagination.

 
Posted: 08 July 2007 05:00 AM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2007-02-07
38 posts

Yeah I do have to use it.

 
Posted: 24 September 2007 11:53 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2007-04-08
27 posts

Try tis code

function getData($num$offset,$totaldata "")
{              
       
  $offset 
= (!$offset) ? $offset;
  
$next = ($num $offset);
  
$orderR "asc";
  if(
$next >= $totaldata)// this code for not repeat the last registres
  
{
     $next 
$totaldata $offset ;
     
$orderR "desc";
  

  $where  
'';
               
$sql "SELECT * FROM (
   SELECT TOP 
$num  * FROM (
     SELECT TOP 
$next *
     FROM nfr_vendors
     
$where
     ORDER BY 
$order $orderR) AS t1
   ORDER BY 
$order DESC) AS t2
ORDER BY 
$order ASC";
$query $this->db->query($sql);       
return 
$query->result(); 
 
 
Posted: 15 July 2008 09:03 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2008-04-20
27 posts

Did anyone get this to work?

 Signature 

I <3 beer, coffee, and Codeigniter
Denver web development | jeremywarren.org
Twitter

 
Posted: 06 April 2010 02:15 AM   [ # 5 ]   [ Rating: 0 ]
Joined: 2010-04-06
8 posts

I did it with a query found at tech-recipes that uses BETWEEN instead of OFFSET.

Since I use MSSQL (my employer uses MSSQL, not necessarily my choice) I have to figure out ways to do some of CI’s MySQL-only stuff, like how OFFSET is normally used easily with the pagination (using the active records class) library. I’ve been learning CI with Jeffrey Way’s CI From Scratch on NetTuts - pagination has been a bit more involved figuring out how to use it with MSSQL. Here’s what I came up with…

class Site extends Controller    {
    
    
function index()
        
{
        $this
->load->library('pagination');
        
$this->load->library('table');
        
        
$this->table->set_heading('ID''The Title''The Content');
        
        
$config["base_url"'http://localhost:8888/ci/index.php/site/index';
        
$config["total_rows"$this->db->get('data')->num_rows();
        
$config["per_page"10;
        
$config["num_links"20;
        
$config["full_tag_open"'<div id="pagination">';
        
$config["full_tag_close"'</div>';

        
// start MSSQL-specific code
        
$table  'data';
        
$fields 'id, title, contents';
        
        
$this->pagination->initialize($config);
        if(
$this->uri->segment(3))
            
{
            $totalTo 
$this->uri->segment(3)+10;
            
}    else    {
            $totalTo 
$config["per_page"];
            
}
        $totalFrom 
$totalTo $config["per_page"1// add one so we don't overlap the number from the last result set
        
        
$data["records"$this->db->query("WITH temp AS
                                            (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rownum
                                            from 
$table)
                                            SELECT 
$fields FROM temp WHERE rownum BETWEEN $totalFrom AND $totalTo");
        
        
$this->load->view('site_view'$data);
        
}
    } 

I’m not sure if it works with MSSQL 2000, but it definitely works with 2005+. If you’re using 2000, you should really look at upgrading anyways raspberry

Hope that helps folks. I seen a few other alternative SQL queries for paginating MSSQL results and they were either sort of heavy SQL-wise or they could slow down performance with large data sets. This SHOULD perform well enough, especially with the correct indexes. Always interested in being better, though, so if someone finds a flaw (with the custom MSSQL parts, not the tutorial parts…) let me know smile