Row Count along with Select - SQL Server Forums

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)

Row Count along with Select

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-09-25 : 14:46:16

Can i generate a colum as serial number along with a result set of any SELECT Statement.. such as Row number !! some how!!

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 15:00:27

In SQL 2000, this is most easily done at the presentation layer -- on the web page, report, application, etc. Doing it in T-SQL can be difficult and very inefficient.

Where are you outputting these results?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 15:13:36

If performance is of no matter,

SELECT IDENTITY(INT, 1, 1) AS RowNumber, *
INTO #Temp
FROM <your data here>
ORDER BY <some column here>

SELECT * FROM #Temp ORDER BY RowNumber

E 12°55'05.25"
N 56°04'39.16"

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-09-25 : 15:17:07

I need this to co-relate this to some other Queries and table result.. I've to negociate with.. thats why.. if i had found some solutions.. It may help me to work on some other part..! On other end i was wondering whether there is any tricky way to resolve this..!
Thank you

quote:
Originally posted by jsmith8858

In SQL 2000, this is most easily done at the presentation layer -- on the web page, report, application, etc. Doing it in T-SQL can be difficult and very inefficient.

Where are you outputting these results?

- Jeff
http://weblogs.sqlteam.com/JeffS

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 15:35:08

You can use select @@rowcount in the next statement.

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-09-25 : 15:36:54

:)
Fine!! Thank you..! Lets see whether any one have any other suggections..
Than you friend.. :)

quote:
Originally posted by Peso

If performance is of no matter,

SELECT IDENTITY(INT, 1, 1) AS RowNumber, *
INTO #Temp
FROM <your data here>
ORDER BY <some column here>

SELECT * FROM #Temp ORDER BY RowNumber

E 12°55'05.25"
N 56°04'39.16"

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-09-25 : 15:39:28

@@RowCount is the total number of rows right!!

quote:
Originally posted by cat_jesus

You can use select @@rowcount in the next statement.

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 15:42:44

It's the total from the previous statement. If you did this:

select * from mytable --and mytable has 1000 rows
select @@rowcount

@@rowcount will be 1000

But if you did this:

select * from mytable where last_name = 'belt' -- and there are 10 people in your table with a last name = 'belt'
select @@rowcount

@@rowcount would be 10

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 15:49:49

well if he's just looking for a unique key and not necessarily a rownumber there's always newid()

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-09-25 : 15:51:24

Sorry, Van!!
I am not asking for total row count rather i need curresponding row number, such as serial number along with rows..
(such as something what Peso suggested.. !!)

quote:
Originally posted by Van

It's the total from the previous statement. If you did this:

select * from mytable --and mytable has 1000 rows
select @@rowcount

@@rowcount will be 1000

But if you did this:

select * from mytable where last_name = 'belt' -- and there are 10 people in your table with a last name = 'belt'
select @@rowcount

@@rowcount would be 10

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 15:53:16

Understood. I was just answering your question where you asked about @@rowcount.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 15:58:45

What is the underlying problem? Perhaps you only think you need a row count in a select statement.

I am reminded of something my grandfather used to say. When your only tool is a hammer, every problem looks like a nail.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 16:04:24

I think he need at least three different answers to be able to pass his class exam.

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 01:29:33

"Decibel" Infotech, huh? Guess that explains all the exclamation points!!!

There is another way... and, in case you come across it, don't use it if it has "<" or ">" or "<=" or ">=" in a correlated subquery. It's a terrible method for running counts and it forms a "triangular join" (a bit more or less than a full Cartesian join). Using it on a little bitty table of only 20,000 rows will generate about 200,030,000 rows of data internally. That will take a bit to resolve.

Peso implied that it's a performance hit... but the method he used is the fastest in SQL Server 2000. Just pipe your query into a temp table using the IDENTITY function, as he did.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 04:24:21

Other approach (assigning randomly and not neccessarily in any particular order)


Alter table your_table add row_number int
Go
declare @i int
set @i=0
update your_table set row_number =@i, @i=@i+1

Select 
       columns 
from 
       your_table
order by 
       row_number

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 19:58:59

Nicely done... can force an order with an index hint... can also be writen as (not including the declarations)...

update your_table set @i = row_number = @i=@i+1

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 02:38:06

quote:
Originally posted by Jeff Moden

Nicely done... can force an order with an index hint... can also be writen as (not including the declarations)...

update your_table set @i = row_number = @i=@i+1

--Jeff Moden

I think I dont understand what you meant by not including the declarations

Did you mean to say it could be rewritten like the following?

declare @i int
set @i=0
update your_table set @i=row_number =@i+1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources