July 28, 2004 at 5:25 am
Hi,
I am having 'emp' table with following records
empno ename age
1001 James 25
1002 Peter 20
1003 Mary 22
1004 Marshal 26
I want to add the S.No column with the recordset and it should contains consecutive number for all records in the recordset.
S.No empno ename age
1 1001 James 25
2 1002 Peter 20
3 1003 Mary 22
4 1004 Marshal 26
Thankz and Regards
-Gopi
July 28, 2004 at 11:11 am
Does your S.No column correspond directly to the empno (1, 1001; 2, 1002...)?
If not, you probably want an identity column which will automatically increment. Otherwise, you could use substring to pull the last digit from empno. By the way, I wouldn't name the new column "S.No", "S_No" would be better. Stuff like spaces, periods, quote marks, etc can cause problems in object names.
Steve
July 28, 2004 at 12:52 pm
hi, I am Guessing that you want to ad a counter at querytime that lists the number of the employees because you have said "add a column to the recordset" If this is the case you can do the primary select into a temp table with an identity column then select from the temp table. This Procedure adds the identity as it iterates throught the Company table in northwind.
Create Proc CompanyFromCountryList @country Varchar(10) as select Identity(int,1,1) as CompanyNumber, CompanyName, ContactName into #temptable from Customers where Country=@country Select * from #temptable |
Hope that Helps
tal McMahon
July 28, 2004 at 10:47 pm
July 28, 2004 at 11:38 pm
Try this
select tempemp.rank,tempemp.empno,ename,age
from emp,(select rank=count(*),empno=A.empno
from (select empno from emp)A,
(select empno from emp)B
where A.empno>=B.empno
group by A.empno)tempemp
where tempemp.empno=emp.empno
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply