counting the rows in the result set

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

Transact-SQL (2000)

counting the rows in the result set

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-26 : 05:30:05

hi,
if I have a simple query like
select a,b,c from table where (some condition)

now can I have an additional colum just like serial number?
meaning that if ten results are shown then it should show it just like an identity column.
thanks

Expect the UnExpected

samrat
Yak Posting Veteran

94 Posts

Posted - 2003-05-26 : 06:00:49

Check this out...

http://www.sqlteam.com/item.asp?ItemID=1491

HTH,

Samrat

Edited by - samrat on 05/26/2003 06:01:14

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-26 : 09:46:05

Hi harshal:

I think this topic has been debated on many times, and its commonly agreed that certain tasks like showing a serially incrementing number, or calculating a running total, are best left to the front-end, especially if you are using a reporting tool like Crystal Reports. These tasks are quite mundane in reporting tools, yet, quite cumbersome to implement in a set-based language like SQL.

OS

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-26 : 09:51:59

I just remembered this...its not a very useful solution but this works:

SELECT IDENTITY(INT, 1, 1) AS CustomerNumber, CustomerID, CompanyName
INTO #TempCustomers FROM Customers

SELECT * FROM #TempCustomers

So if you are selecting data into another table (probably in a sproc), you can use the IDENTITY function to generate the serial values.

OS

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-27 : 05:22:33

quote:

I just remembered this...its not a very useful solution but this works:

SELECT IDENTITY(INT, 1, 1) AS CustomerNumber, CustomerID, CompanyName
INTO #TempCustomers FROM Customers

SELECT * FROM #TempCustomers

So if you are selecting data into another table (probably in a sproc), you can use the IDENTITY function to generate the serial values.

OS

this is similar to http://www.sqlteam.com/item.asp?ItemID=1491
I agree with i that it should be taken care by the front end ,since we are using .net and the results were to be dynamically shown on the page I was trying out for different solutions .any ways we have done it in front end.
thanks for the response.
regards,
harshal.

Expect the UnExpected

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources