Including Sl.No column with the record set

  • 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

  • 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

  • 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


    Kindest Regards,

    Tal Mcmahon

  • Thanq Tal Mcmahon,

    very very thankz. This site is very very useful to me.

     

    Regards

    -Gopi

  • 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