generate serial number for a recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

generate serial number for a recordset

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-28 : 08:21:40

pradeep writes "I really depend on the people of sqlteam for my question.
Please help me out

i have got a recordset based on a query
I need to generate the corresponding serial number for the purpose of reports..
like i have a table

TABLE table1
{
col1 int
col2 char
col3 char
}

i have a query that filters the table depending on a number of conditions for col2 and col3

The resulting recordset should have ideally 3 columns like
-----------------------------------------
sl.no col2 col3
-------------------------------------------
(This will look like the report i want!!)

where col2 and col3 are from the table and the "sl.no" is auto generated

The ideal report will be

----------------------------------------------
sl.no col2 col3
----------------------------------------------
1 raghu has a car
2 deep has a bike

Thanks in advance"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-28 : 20:46:10

I find your question a little confusing. Do you want your report records numbered? Or do you want to generate a unique serial number to be stored with each record?

For a unique number (serial no) to be stored with each record, add a column to your table which is of type GUID.

For row numbers on your report, use a temp table - like this:

create table #a (reportrownumber int identity(1,1), col2 char, col3 char)
insert into #a (col2, col3)
select col2, col3
from table1
where ...
order by ...

select * from #a

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-04-29 : 02:12:51

SELECT SlNo=Count(*), P.name
FROM table1 P
INNER JOIN table1 P1
ON P.col1 >= P1.col1 and p.col2>=p1.col2
and p.col3>=p1.col3
GROUP BY p.col2,p.col3
ORDER BY 1

But this will be a performance cry
HTH

--------------------------------------------------------------

Edited by - Nazim on 04/29/2002 02:17:34

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-04-29 : 04:12:43

Hi Gurus,
I think this can be solved using the identity function.

select identity(int,1,1) 'SlNo', col2,col3 into #MyReport from table1
Select * from #MyReport

S.Mohamed Yousuff

quote:

pradeep writes "I really depend on the people of sqlteam for my question.
Please help me out

i have got a recordset based on a query
I need to generate the corresponding serial number for the purpose of reports..
like i have a table

TABLE table1
{
col1 int
col2 char
col3 char
}

i have a query that filters the table depending on a number of conditions for col2 and col3

The resulting recordset should have ideally 3 columns like
-----------------------------------------
sl.no col2 col3
-------------------------------------------
(This will look like the report i want!!)

where col2 and col3 are from the table and the "sl.no" is auto generated

The ideal report will be

----------------------------------------------
sl.no col2 col3
----------------------------------------------
1 raghu has a car
2 deep has a bike

Thanks in advance"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-29 : 04:19:56

Well done Yousuff. Looks like exactly what I typed... we must be in agreement.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources