|
Emulating the mySql limit function in MS SQL |
||||||||
There are pro's and con's to both mySql and MS SQL, one of the pro's of mySql is the limit function. This allows you to set a starting record number, and select N number of records from there. But how can we do that in MS SQL?
This is usually used for pagination functions, IE SELECT the next N records from the current count.
Firstly declare two variables, a "rows per page" and a start record.
2<cfset rowsperpage = 15>
3
4DECLARE @rowsperpage INT
5
6 DECLARE @start INT
7
8 SET @start = #startpos#
9 SET @rowsperpage = #rows#
Next we need to write the query we want, but wrap it in a parent select. Remember at this point to do all your conditional processing on the query inside the parenthesis.
2
3 (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
4
5 FROM [table]
6 Where [table].intId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.intId#">) AS tempQuery
Next we can apply a where clause that filters down the results based on the two variables declared at the top of the script. This means we will only get back the required number of records, starting at our defined record number.
2Order by rownum
The whole script looks like this:
2<cfset rowsperpage = 15>
3
4DECLARE @rowsperpage INT
5
6 DECLARE @start INT
7
8 SET @start = #startpos#
9 SET @rowsperpage = #rows#
10
11SELECT * FROM
12
13 (SELECT row_number() OVER (ORDER BY [intId]) AS rownum, *
14
15 FROM [table]
16 Where [table].intId = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.intId#">) AS tempQuery
17
18Where tempQuery.rownum BETWEEN (@start) AND (@start + @rowsperpage)
19Order by rownum
I am selecting *, but I recommend that you actually list your fields here, I tweaked it for this entry.
Not quite as nice as the mySql version tho is it:
2From table
3Limit 0,100
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]