Post reply

ROW_NUMBER equivalent in SQL Server 2000?

  • August 25, 2005 at 9:08 am

    #363489

    Greetings all.

    Does anybody know of an elegant way in SQL Server 2000 of achieving the results of the new ROW_NUMBER() function in SQL Server 2005?

    If you are not familar with ROW_NUMBER() in SQL Server 2005, it apparently allows you to sequentially number the rows in a result set of a SELECT statement.

    I've checked around and can't seem to find any easy way to do this unless I use a temp table and an identity field, which is not the greatest solution.

    You may be thinking "Why not wait for 2005?". Actually, I'm looking into this for one of my colleagues, who is hoping for a solution soon.

    Thanks.

    - Mike

  • August 25, 2005 at 9:13 am

    #585033

    The solution is do it client side... unless that impossible in that case. What is the base select statement?

  • August 25, 2005 at 9:16 am

    #585038

    you could do something like this:

    SELECT IDENTITY(INT,1,1) AS Row_Number, *
INTO #Results
FROM tblMytable
WHERE ....
 

SELECT *
FROM #Results
ORDER BY Row_Number







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 25, 2005 at 9:24 am

    #585047

    Thanks ... I was aware of the Identity/temp table solution but was hoping for a single statement solution.

    It sounds like it isn't possible. That's okay ... I was just wondering.

    P.S. The guy who wants to do this tells me it is not something he could do on the client side, as suggested in the first reponse.

    Thanks again.

    - Mike

  • August 25, 2005 at 9:27 am

    #585049

    What is the base select statement?

  • August 25, 2005 at 9:34 am

    #585051

    Remi,

    I hadn't even got that far with the user. I didn't think it was particularly relevant.

    Thanks for your interest nonetheless. I don't think I need anything further.

    - Mike

  • August 25, 2005 at 9:41 am

    #585055

    I only ask because it's quit easy to do set based >>

    Select O1.id, O1.name, count(*) as Rank from dbo.SysObjects O1 inner join dbo.SysObjects O2 ON O2.id <= O1.id group BY O1.id, O1.Name Order By O1.id

  • August 25, 2005 at 9:46 am

    #585058

    Interesting. Thanks!

  • August 25, 2005 at 9:48 am

    #585062

    HTH.

  • August 25, 2005 at 9:57 am

    #585068

    rg - i had forgotten about this even though it has been addressed in quite a few posts...this is why you're the rg and i'm just brain-dead!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 25, 2005 at 10:01 am

    #585073

    My solution is not too bad but yours can be better. The join can take up a lot of ressource. Even with only 5000 rows in the resultset, you'll actually get 12507500 rows from the base table before the inner join is made... That can become extremely slow as you add more rows .

    P.S. I had almot forgot that rg = resident genius .

  • August 26, 2005 at 12:27 pm

    #585556

    i have to do sequencing on the query below

    select orroomname, tblsortorder.orroomno,atime, dtime, caseid from tblcasedata  inner join tblsortorder

    on tblcasedata.orroomno= tblsortorder.orroomno

    where dateofservice= '8/11/2005'

    and tblsortorder.orroomno in ('611','554')

    and qcstatus=4

    order by orroomname, atime

    i am getting out put as

    Orromname  orroonno              atime                  dtime            caseid

     MRI   611                      2005-08-11 12:00:00.000 2005-08-11 14:00:00.000 OMOR12232

     MRI   611                      2005-08-11 15:15:00.000 2005-08-11 17:45:00.000 OMOR12233

     NPTC 554                      2005-08-11 09:15:00.000 2005-08-11 10:26:00.000 OMOR12234

     NPTC 554                      2005-08-11 10:27:00.000 2005-08-11 11:06:00.000 OMOR12235

     NPTC 554                      2005-08-11 11:09:00.000 2005-08-11 11:39:00.000 OMOR12236

     NPTC 554                      2005-08-11 12:01:00.000 2005-08-11 12:44:00.000 OMOR12237

     NPTC 554                      2005-08-11 12:46:00.000 2005-08-11 13:50:00.000 OMOR12238

    i want one more field as seqno and wanted 1,2 for  orroomno 611 and 1,2,3,4 for orroom no 554

    could anybody change my query and help

    thanks

     

  • August 26, 2005 at 12:30 pm

    #585557

    Check this thread out :

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=189202

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply