January 17, 2002 at 11:07 am
How can I modify "Select * from TableName" to display line numbers in front of each row. For example, I'd like:
1 field1 field2 field3
2 field1 field2 field3
3 field1 field2 field3
The row numbers reflect the order of the rows in the database. I'm not trying to sort, group or do anything fancy to the data.
TIA,
Bill
January 17, 2002 at 12:55 pm
SELECT ISNULL
((SELECT COUNT(*)
FROM aaTABLE1 AS a2
WHERE a2.a < a1.a), 0), *
FROM aaTABLE1 AS a1
December 8, 2002 at 7:04 pm
quote:
SELECT ISNULL((SELECT COUNT(*)
FROM aaTABLE1 AS a2
WHERE a2.a < a1.a), 0), *
FROM aaTABLE1 AS a1
While this works, be aware that you have one select statement executed for every row returned. If your query returns 1 million rows, you're going to do 1 million selects. If each of those has to select 1 million records ......
well, that's whay we hve the word "trillion" in English. I hope you have a multi-CPU machine.
December 9, 2002 at 7:52 am
Other options are to insert into a table variable with an identity column and return that.
Other is to join to a qub qeury similar to above
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply