0

Please see below example:

<cfquery name="getAllMembers" datasource="#application.dsn#">
    SELECT  DCNumber,RegDate,gender,FirstName,LastName,(select count(*) from DriversRecord) as totalCount
            FROM DriversRecord

    LIMIT <cfqueryparam value="#start#" cfsqltype="cf_sql_integer" />,<cfqueryparam value="#paginationArgs.rowsPerPage#" cfsqltype="cf_sql_integer" />
</cfquery>

How can I covert this query which is in MY SQL But I am using MS SQL 2012 MS SQL?

fedorqui 'SO stop harming'
  • 228,878
  • 81
  • 465
  • 523
Brusk
  • 1
  • 1
  • Going with what I know, which is not very much, I'd look at sql server's rank function. – Dan Bracuk Sep 17 '14 at 13:23
  • 2
    This isn't a ColdFusion question, is it? Isn't it just T-SQL? – Adam Cameron Sep 17 '14 at 14:02
  • Thank you Adam, I did go through http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server , before I post this Question, but it didn't work for me. – Brusk Sep 17 '14 at 14:43
  • Thank you Adam and Dan, I did go through http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server , before I post this Question, but it didn't work for me. I am sorry I am new to ColdFusion,how can i make this query to work in MS SQL – Brusk Sep 17 '14 at 14:50
  • I did try this but now luck SELECT DCNumber,RegDate,gender,FirstName,LastName,(select count(*) from DriversRecord) as totalCount FROM DriversRecord where totalCount between AND – Brusk Sep 17 '14 at 14:52
  • Previous to SQL Server 2012, I'd say the linked answer would be appropriate, but with 2012, there is now OFFSET-FETCH available which makes pagination almost as easy. http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx **SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;** – Adrian J. Moreno Sep 17 '14 at 15:15
  • If you are able to edit closed questions, please edit this one to show your attempt to use the answer from the other thread. Also be more specific than "it didn't work for me". – Dan Bracuk Sep 17 '14 at 17:58

0 Answers0