MSSQLTips

I have one table member. I want to retrieve the query result along with one colum containing serial numbers. Suppose for

case1: select * from member where EnrolledDateTime='2008-06-30' ------Suppose This query gives 30 records

case2: select * from member where EnrolledDateTime='2008-07-01' ------Suppose This query gives 100 records

I want the result set in first case containg 1 to 30 serial numbers and in second case the result must contain 1 to 100 serial number

Have u any idea about this ,Please help me as i want this

 Which version of SQL Server?

Microsoft SQL Server 2000 - 8.00.760 and with Service Pack 4

Hi,

    Unfortunately in 2000 rownumber concept is not available while you retrieve the data..So you need to create a temp table with identity column, dump the result set into that table and retrieve from that temptable...

    Create table #tmptbl (idcolumn int identity (1,1),....<column list>)

    insert into #tmptbl select * from <maintable>

   select * from #tmptbl  -- where idcolumn contains the row numbers....

  where as in 2005 it is straightforward.. 

   select row_number() Over (order by <column> desc) as rownumber,<column list> from <tablename>  

 

Thanks,

Narchand 

 

 

 

No idea what your table looks like but I've used something like this in the past with SQL 2000 

select rownumber = (select count(*) 
                    from member m2
                    where m2.memberid >= m1.memberid
                    and EnrolledDateTime = '2008-06-30'),
       *
from member m1
where EnrolledDateTime = '2008-06-30'
order by rownumber
 

Select row_number() over(partition by EnrolledDateTime) as srl, <field list> from member

Just use the following sql queries together in sql server 2000.

SELECT ID=identity(int,1,1),* INTO #temp FROM Table_Name

drop table #temp