Skip to main content
L
La_Salamandra
Participating Frequently
February 14, 2010
Question

sql select "limit" in MSSQL

  • February 14, 2010
  • 4 replies
  • 3730 views

Hi,

I use MSSQL wit coldfusion.

I would like to know if there is a way to use the "limit" clause as in MySQL.

Example:

I have 1000 record to show, 10 per page.

page 1) select * from table limit 1,10

page 2) select * from table limit 11,10

page 3) select * from table limit 21,10

...

page N) select * from table limit (N-1)x10+1, 10

Maybe with the TOP clause? But how?

Thanks in advance.

This topic has been closed for replies.

4 replies

A
A***
Inspiring
February 15, 2010

Is this what you are looking for i.e. links to different pages with corresponding query output at 10 records per page?

L
La_SalamandraAuthor
Participating Frequently
February 15, 2010

Yes, something like that.

A
A***
Inspiring
February 15, 2010

Let us assume we are retrieving columns EmployeeID, Title and HireDate from table Employee on MSSQL database AdventureWorks.

<!--- Specify columns --->

<cfquery datasource="AdventureWorks" name="myRecords">

      SELECT TOP 150 EmployeeID, Title, HireDate

      FROM Employee

</cfquery>

<cfif myRecords.recordcount EQ 0>

      Sorry!<br />

      There are no records yet on the database.

<cfelse>   

      <!--- Set default value of 0 for a "pageindex" parameter. This is the page that will have the first set of records --->

      <cfparam name="URL.PageIndex" default="0">

     

      <!---

1. Set the number of records to be displayed per page as ten

2. Calculate total "number of pages" by dividing "total records retrieved" by "Number of records per page"

3. Set "StartRow" of records:

   StartRow = (PageIndex X RecordsPerPage) + 1 i.e. on the first page StartRow will be 1 as PageIndex is 0

   On the second page, PageIndex will be 1, and StartRow value will be (1*10)+1 = 11 etc.

4. Set "EndRow" as StartRow+RecordsPerPage-1. So if StartRow = 11, and Number Of Records per page = 10, EndRow value = 11+10-1 = 20 etc.

      --->

<cfset RecordsPerPage = 10>

      <cfset TotalPages = (myRecords.Recordcount/RecordsPerPage)>

      <cfset StartRow = (URL.PageIndex*RecordsPerPage)+1>

      <cfset EndRow = StartRow+RecordsPerPage-1>

     

      <!---

      Display records if CurrentRow is greater than or equal to StartRow value

      As long as CurrentRow is less than EndRow, loop thru query

      When CurrentRow equals EndRow you are finished with current page --->  

      <cfoutput>

            <table cellpadding="2">

                  <tr><td>Employee</td><td>Position held</td><td>Date employed</td></tr>

                        <cfloop query="myRecords">

                              <cfif CurrentRow gte StartRow>

                                    <tr><td>#myRecords.EmployeeID#</td><td> #myRecords.Title#</td><td>#lsDateFormat(myRecords.HireDate, 'dd mmm yyyy')#</td></tr>

                              </cfif>

                              <cfif CurrentRow eq EndRow>

                                    <cfbreak>

                              </cfif>

                        </cfloop>

            </table>

      </cfoutput>

     

      <!--- Display page number links. Since the default page index (0) is also the page displaying the 1st set of records, we start our loop from 0

      ---> 

      <br />

      <cfloop index="Pages" from="0" to="#ceiling(TotalPages)-1#">      <!--- Loop through the exact number of pages --->

            <cfoutput> |

                  <cfset DisplayPgNo = Pages+1>

                  <cfif URL.PageIndex eq pages>

                        <span><strong>#DisplayPgNo#</strong></span>

                        <!--- Display links for all PageIndexes other than current page --->

                        <cfelse>

                        <a href="#script_name#?PageIndex=#Pages#">#DisplayPgNo#</a>

                  </cfif>

            </cfoutput>

      </cfloop> |

</cfif>

L
La_SalamandraAuthor
Participating Frequently
February 14, 2010

Thanks a lot for your answers.

That's really a pity. "Limit" clause is really a good way to avoid to call in memory 1000 records each time you change the page clicking "next 10".

One workaround could be the use of "where id > N" with TOP. I'd like to know what do you think about it.

Example:

Page 1) select top 10 * from table

            set last_id = row_10_id

Page 2) select top 10 * from table where id > last_id

            set last_id = row_20_id

Page 3) select top 10 * from table where id > last_id

            set last_id = row_30_id

....

Page N) select top 10 * from table where id > last_id

             set last_id = row_(Nx10)_id

D
Dan_Bracuk
Inspiring
February 14, 2010

Seems to me that it would be much simpler to simply run the query once as a session variable and use ColdFusion code to select the records for each page.

L
La_SalamandraAuthor
Participating Frequently
February 14, 2010

It would be nice. But I really don't know how to do. Could you make me a example, please?

Will it works also in a cluster of servers?

-
-__cfSearching__-
Inspiring
February 14, 2010

But I really don't know how to do. Could

you make me a example, please?

Did you read the link? It shows exactly how to make it work with different versions of MS SQL.

A
Adam Cameron.
Inspiring
February 14, 2010

This is a bit of a SQL Server question rather than a CF question: it might be worth asking on a SQL-Server-specific forums.

There's no native way of effecting the same results as a LIMIT clause by using SQL Server's TOP.  TOP only takes the top (oddly enough), and only takes the one parameter.

There are ways around this (they're all pretty grim)...the last time I checked one had to mess around with subqueries and inverse sorting and TOP to lop off each end of a recordset in turn to end up with the middle "slice" of the recordset that one would get with LIMIT n,m

Try googling "sql server top limit" or something like that to see what I mean.

--

Adam

-
-__cfSearching__-
Inspiring
February 14, 2010

There are ways around this (they're all pretty grim)...the

last time I checked one had to mess around with subqueries

and inverse sorting and TOP to lop off each end of a

recordset in turn to end up with the middle "slice" of the

recordset that one would get with LIMIT n,m

It gets a bit better in 2005+, as you can take advantage of the ROW_NUMBER() function. I do not know about 2008. While still not built-in functionality, definitely a little less convoluted.

http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices