Writing a LIMIT Query with PHP and MSSQL

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I've searched here and Google for a solution to this. The ones I found I don't like and I want to see if there is a better way. I have an MSSQL database, the first one I've worked with, that I connect to using PHP. It was a small pain getting PHP to work with it but I've configured worse things. As I've been working with MSSQL, I've come to like several things about it. Then, this. How does a SQL not offer a LIMIT and Offset!? The workarounds I found are borderline crazy when you juxtapose the MSSQL query with what can be accomplished with MySQL's LIMIT. I just couldn't make myself implement it until I posted here to see if a MS or SQL guru knows a simpler method without all the nested queries. I thought my saving grace would be PHP's mssql_fetch_batch() but it doesn't seem to be working for me. I was actually using the batch initially and swapped to SELECT TOP n records just to make sure I was getting some data, which I was. I then tried to offset and ran into this problem.

Also, The batch seems to still retrieve all the data. I'm trying to fetch many records without increasing the memory on the server a great deal. Am I better off with a batch and if not how do I use an offset query to pull only a few records at a time like pagination?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6243
  • Loc: Seattle, WA

Post 3+ Months Ago

SQL Code: [ Select ]
SELECT TOP 10 *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow
  1. SELECT TOP 10 *
  2. FROM (
  3.     SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
  4.     FROM MyTable
  5. ) AS MyDerivedTable
  6. WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow


http://stackoverflow.com/questions/1879 ... sql-server
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Can't wait to try it. I saw several posts on stackoverflow but I did not see this query. Some of the ones I saw just got way out of hand. This one isn't too bad. It should work nicely. Thanks for the post.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6243
  • Loc: Seattle, WA

Post 3+ Months Ago

No problem. I always assumed LIMIT was part of the SQL standard, when, in fact, it isn't.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 114 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.