Rewrite this SQL for sql 2000

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

LVL 8
CamilliaAsked:
Who is Participating?
 
Jesus RodriguezIT ManagerCommented:
0
 
Jesus RodriguezIT ManagerCommented:
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
 
CamilliaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.