Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Karachi Pakistan
    Posts
    83

    Unanswered: Oracle's ROWNUM equivalent in SQL Server

    Hi,

    Can any one tell me is there anything in SQL Server thats equivalent
    to Oracle's ROWNUM.

    Note that the Identity Property or TOP n will not solve my problem.

    I want to asign a sequence no. to each row when its being fetched.

    For example if in the emp table there are 2000 rows and I write
    the following query in Oracle ,

    SELECT rownum , empno, empname FROM emp Where rownum < =3

    I get the result like this

    Rownum----Empno--------------Empname
    ------------------------------------------
    1-----------2345---------------ABCD
    2-----------3334---------------EFGH
    3-----------4484---------------IJKL


    I know I can limit the output rows in SQL Server by using TOP n. But
    I also want to generate a sequence no. The identity property of SQL Server
    will not be usefull here because my actaul WHERE clause will be more
    complex like WHERE resigndate = '01-jan-2004'

    Thanks

    Asim Naveed

    3
    Last edited by asim73; 09-02-04 at 04:03.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    A frequently asked question...

    The answer usually involves creating a temp table (or table function) with an identity column. Insert the results of your query into this temp table and you'll have more or less what you need.

    Hopefully this is something that is addressed in 2005?

    hmcott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is actually a good reason for this "limitation" to exist. If the result set is being used on the server, it has to go into a table anyway, so the IDENTITY solution works nicely. If the result set is being used on a client, then the client ought to provide the rowid values (since the client knows more about how it wants the ids arranged than the server can).

    This is actually a good design property, even though applications being moved from database managers like dBase and Oracle are often dependant on physical details that they shouldn't rely on.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •