Creating an Offset in MS SQL

Posted on Oct 14, 2005

Sorry for the lack of posts recently, but I have been stuck in the land of copy/paste internationalization (which is both a bad way to do things and pure torture to implement...but more on that later). Anyway, I was modifying Barney Boisvert's TreeManager CFC for a side project I still maintain. The component was written for MySQL but for the most part the only MySQL specific code appears to be the use of LIMIT instead of TOP. However, in one case (sortChildren()) he uses LIMIT with the following syntax: LIMIT 5,1. Well, in case you aren't aware (I wasn't), the first item in the list is an offset (i.e. it will get the top 1 after the first 5). Turns out that there does not appear to be a MS SQL equivalent to this (Pete Freitag had discussed this and asked about a MS SQL equivalent but I did not see an answer). However, the following query appears to work (it was tested against the categories table in Northwind):

SELECT TOP 1 *
FROM categories
WHERE categoryid NOT IN (SELECT TOP 4 categoryid FROM categories)

Using that, you could change lines 447-451 of treemanager.cfc to:

SELECT TOP 1 #variables.id# AS id
FROM #table#
WHERE #variables.id# IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#getChildIds(parentID)#" list="true" />)
AND id NOT IN (SELECT TOP #i - 1# #variables.id# FROM #table#)
ORDER BY #lpos#

I am in the early stages, so I haven't tested that yet, but it should work. If anyone knows a better method to acheive this, please let me know. Once I get all the code MS SQL compatible in treemanager.cfc, I hope to send it off to Barney so he can make it available.

Comments

Graham Beech Thanks this is the only solution for seeting an offset in MS SQL i could find... think ORDER BY needed adding in both statements to keep offset order correct, (eg inside and outside brackets) and any other Where clauses twice also.

Posted By Graham Beech / Posted on 03/13/2006 at 7:05 AM

Martin Dahl-Larsen Very cool way to do it, i use it ;-)

Posted By Martin Dahl-Larsen / Posted on 12/07/2008 at 7:40 AM

bookstore software i am confused here ...i needed something equivalent to mysql "SELECT * from tablename LIMIT 10, 20"

Posted By bookstore software / Posted on 02/01/2010 at 6:33 AM

Sava Dimitrov Thank you very much. Very nice solution :) Thanks

Posted By Sava Dimitrov / Posted on 09/08/2011 at 12:29 AM

Write your comment



(it will not be displayed)





About

My name is Brian Rinaldi and I am the Web Community Manager for Flash Platform at Adobe. I am a regular blogger, speaker and author. I also founded RIA Unleashed conference in Boston. The views expressed on this site are my own & not those of my employer.