Number you eggs before they hatch! :)) Bad one!!
I search a lot about giving row number to each resultant row of select statement but it seemed such automatic row numbering function is not available in T-SQL (SQL which runs with MS SQL server). But my colleague, Subamanin, gave a wonderful solution which I want share with you. Code goes like this notice the ROW_NUMBER() function.
CODE:
declare @EMP table
(
emp_name VARCHAR(10),
emp_post varchar(10)
)
insert into @EMP values('A11','00A')
insert into @EMP values('A12','00B')
insert into @EMP values('A13','00C')
insert into @EMP values('A14','00A')
insert into @EMP values('A14','00A')
select *
from @EMP --Produces Table(0)
select *,
ROW_NUMBER() over(order by emp_name) as ID_NUM
from @EMP --Produces Table(1)
And this is RESULT:
TABLE(0)
| emp_name | emp_post |
| A11 | 00A |
| A12 | 00B |
| A13 | 00C |
| A14 | 00A |
| A14 | 00A |
TABLE(1)
| emp_name | emp_post | ID_NUM |
| A11 | 00A | 1 |
| A12 | 00B | 2 |
| A13 | 00C | 3 |
| A14 | 00A | 4 |
| A14 | 00A | 5 |
But unfortunately over(order by emp_name) function has to be there. Here in 'over' function you can order by column of your choice which may or may not be your primary or unique key.
