SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Row Count along with Select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aiby
Yak Posting Veteran

India
71 Posts

Posted - 09/25/2007 :  14:46:16  Show Profile  Visit Aiby's Homepage  Reply with Quote
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

Edited by - Aiby on 09/26/2007 14:50:11

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/25/2007 :  15:00:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/25/2007 15:00:54
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/25/2007 :  15:13:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Aiby
Yak Posting Veteran

India
71 Posts

Posted - 09/25/2007 :  15:17:07  Show Profile  Visit Aiby's Homepage  Reply with Quote
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


Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 09/25/2007 :  15:35:08  Show Profile  Reply with Quote
You can use select @@rowcount in the next statement.
Go to Top of Page

Aiby
Yak Posting Veteran

India
71 Posts

Posted - 09/25/2007 :  15:36:54  Show Profile  Visit Aiby's Homepage  Reply with Quote
:)
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
Go to Top of Page

Aiby
Yak Posting Veteran

India
71 Posts

Posted - 09/25/2007 :  15:39:28  Show Profile  Visit Aiby's Homepage  Reply with Quote
@@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
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 09/25/2007 :  15:42:44  Show Profile  Reply with Quote
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
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 09/25/2007 :  15:49:49  Show Profile  Reply with Quote
well if he's just looking for a unique key and not necessarily a rownumber there's always newid()
Go to Top of Page

Aiby
Yak Posting Veteran

India
71 Posts

Posted - 09/25/2007 :  15:51:24  Show Profile  Visit Aiby's Homepage  Reply with Quote
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
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 09/25/2007 :  15:53:16  Show Profile  Reply with Quote
Understood. I was just answering your question where you asked about @@rowcount.
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 09/25/2007 :  15:58:45  Show Profile  Reply with Quote
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.

Edited by - cat_jesus on 09/25/2007 15:59:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/25/2007 :  16:04:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 09/26/2007 :  01:29:33  Show Profile  Reply with Quote
"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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/26/2007 :  04:24:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 09/26/2007 :  19:58:59  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/27/2007 :  02:38:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 09/27/2007 02:40:28
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000