?
Solved

Rewrite this SQL for sql 2000

Posted on 2012-08-31
3
Medium Priority
?
373 Views
Last Modified: 2012-08-31
I have this SQL that someone wrote for SQL 2005. I want to move this to SQl 2000, how can I remove that Row_Number?


SELECT top 100 percent ROW_NUMBER() over (order by userid, transactiondate) AS id,
       *
FROM   (
           SELECT orderID AS Orders,
                  sum(qty) AS Items,
                  max(transactiondate) AS transactiondate,
                  max(updateuserid) AS UserID
           FROM   <sql2000 database table> ods
           WHERE  convert(varchar,transactiondate,101) between @STARTDATE and @ENDDATE
           GROUP BY
                  orderID
       ) s1
ORDER BY
       userid,
       s1.transactiondate

Open in new window

0
Comment
Question by:Camillia
  • 2
3 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38354852
SELECT TOP 100 PERCENT ID=COUNT(*)
       FROM ( SELECT orderID AS Orders,
                  sum(qty) AS Items,
                  max(transactiondate) AS transactiondate,
                  max(updateuserid) AS UserID
           FROM   <sql2000 database table> ods
           WHERE  convert(varchar,transactiondate,101) between @STARTDATE and @ENDDATE
           GROUP BY
                  orderID
       ) s1
       GROUP BY userid, s1.transactiondate
       ORDER BY
       userid,
       s1.transactiondate


Look as this link
http://support.microsoft.com/kb/186133
0
 
LVL 7

Author Comment

by:Camillia
ID: 38354914
all the IDs come out as 1. In the original one, it's a sequential number...starting from 1 ..so 1, 2, 3, etc
0
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 2000 total points
ID: 38355111
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question