SQL Server Performance

row number issue

Discussion in 'SQL Server 2008 General Developer Questions' started by kdineshbabuct, Aug 6, 2009.

  1. kdineshbabuct New Member

    Hi, I need to print row no in sql server 2005. There is no row_number function in sql server.
    I can to it using some round about method. select Row_Number () over (order by empid,siteid) as rownumber,age,name from employee order by empid,siteidActual resultrownumber name age
    4 aaa 55
    3 aaa 54
    1 aaa 52
    2 jj 05 if I use order by in last it will print in order, as I expect but rownumber not in sequence
    select Row_Number () over (order by empid,siteid) as rownumber,age,name from employeeif i remove order by in this query as above ,it prints rownumber in sequence but order is differnt. please give me a solution
    Note : I think we can implement this using temp table with identity column but i need this in query
  2. FrankKalis Moderator

    Sorry, might be because it's late in my place, but what is now your issue exactly? Given your sample what do want to see as final resultset?
  3. kdineshbabuct New Member

    I need to print data with rown number in sequence with my expected order
    table employee
    name age siteid
    ram 22 33
    sundar 66 22
    mahesh 33 22
    ravi 22 45
    expected output
    rno name age siteid
    1 mahesh 22 33
    2 ravi 22 45
    3 ram 33 22
    4 sundar 66 22
    i need to print data with sequence rowno and order by (siteid,age)
  4. FrankKalis Moderator

    The data in the table you've posted does not match your expected output. In the table it says 'mahesh', 33, 22 while in the expected output it appears as 'mahesh', 22, 33.Same goes for 'ram', so I suspect you mixed them up.
    If so, this should work:
    select Row_Number () over (order by age, siteid) as rownumber,name, age, siteid from employee order by age, siteid

  5. Madhivanan Moderator

  6. kdineshbabuct New Member

    Thanks for ur valuable reply. U r asset for this forum. your answers are really good .

Share This Page