|
Count Row - one by one
|
Email
|
| Submitted on: |
3/24/2004 10:05:50 AM |
| By: |
Israel Balderrama
|
| Level: |
Beginner |
| User Rating: |
By 7 Users |
| Compatibility: |
SQL Server 2000, SQL Server 7.0, SQL Server 6.5 and earlier |
| Views: |
29217 |
|
|
|
|
|
This small code helps you to do a query that count all rows, using the statement COUNT. Is very easy and simple, but maybe could be very useful.
|
 |
| |
Terms of Agreement:
By using this article, you agree to the following terms...
- You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
- You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
- You may link to this article from another website, but ONLY if it is not wrapped in a frame.
- You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
|
SELECT
(SELECT COUNT(*)
FROM authors e2 WHERE e2.au_id <= e.au_id) AS rownumber,
au_id, au_lname
FROM authors e
ORDER BY au_id
|
|
Other 3 submission(s) by this author
|
|
Report Bad Submission
|
Your Vote
|
| |
Other User Comments
|
 4/1/2004 7:11:42 PM:
wouldn't this sql take a lot of system resources? YOu basically count all rows above each row.
(If this comment was disrespectful, please report it.)
|
4/16/2004 8:51:45 AM: Christina McEntire
Although this may be inefficient to run on a large number of rows, sometimes you need to know the position of a record in a recordset, and this could be really useful there. An efficient way to use this query would be to return the row number for a SINGLE record by adding a where clause to the query to filter the record you need.
(If this comment was disrespectful, please report it.)
|
5/7/2004 6:04:53 AM: Peter Schmitz
Itzik Ben-Gan released a basic explanation about this process, as well as offering some insight into some new Yukon functionalities and procedures. You can find his article on www.sqlmag.com.
(If this comment was disrespectful, please report it.)
|
5/17/2004 8:00:26 AM:
really wonderful, thank you
(If this comment was disrespectful, please report it.)
|
3/31/2005 1:56:29 AM:
code is very nice
(If this comment was disrespectful, please report it.)
|
7/4/2006 12:38:22 PM: alyy
its utter waste .........!
(If this comment was disrespectful, please report it.)
|
10/20/2006 5:37:30 AM: anilsingh1981@rediffmail.com
The other method of doing is to use temp table and insert all the data in to temp table which contains one column as auto increment.
script:
create table #temp (S_no int IDENTITY (1300000, 1) NOT NULL,name varchar(25))
go
Insert in to temp select name from tablename
This process will be faster and can be created on fly for the use.
Any suggestion are welcomed.
Thanks and regards
(If this comment was disrespectful, please report it.)
|
10/20/2006 10:26:22 AM: Israel Balderrama
Yes man, its better as your you say, check this other version: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=939&lngWId=5
(If this comment was disrespectful, please report it.)
|
11/27/2007 11:04:28 PM: billy
thank u
(If this comment was disrespectful, please report it.)
|
6/24/2011 10:35:25 AM: arman
very good.thank for ADVICE:-)
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
To post feedback, first please login.
|
