top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to generate row number in SQL Without ROWNUM?

+2 votes
2,433 views
How to generate row number in SQL Without ROWNUM?
posted Jan 19, 2014 by Vishvachi Tiwari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

Generating a row number – that is a running sequence of numbers for each row is not easy using plain SQL. In fact, the method I am going to show below is not very generic either. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique. Anyway, here is the query:

SELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num

answer Jan 19, 2014 by Atul Mishra
Similar Questions
+2 votes
How to restore the SQL server database without log backup?

We have full backup and no log backup can I restore the database?

+1 vote
How to convert multiple rows into single row in iformatica for large volume of data, need best solution

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

...