Unique ID added to a result set

[GM]

Hello all, i have a question i've beenk thinking for a little bit and
cant seem to come up with a solution. What i want to do is add a unique
counter to a resultset in query. For example

SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM AnyCombinationOfJoinsOrTables
WHERE SomeConditionIsTrue

w
The query above will produce a resultest of 4 columns.

'A1', 'B1', 'C1', 'D1'
'A2', 'B2', 'C2', 'D2'
'A3', 'B3', 'C3', 'D3'
......
'An', 'Bn', 'Cn, 'Dn'

Now what i want to do is add a unique identifier to every row. The
easiest way would be to add a counter integer so the resultset would
look like

1, 'A1', 'B1', 'C1', 'D1'
2, 'A2', 'B2', 'C2', 'D2'
3, 'A3', 'B3', 'C3', 'D3'

......
n, 'An', 'Bn', 'Cn, 'Dn'
Is there a way to achieve this in T-SQL without using cursors.

Thanks,

Gent

[David Portas]

Best would be to do it client side. The numbering implies some kind of
order but there is no ORDER BY clause in your example query. Here's an
example:

SELECT
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id <= A.au_id) AS row_num,
*
FROM pubs.dbo.authors AS A
ORDER BY au_id ;

--
David Portas
SQL Server MVP
--

[GM]

David, thank you for the response. That is pretty slick and works for
my example however i did not specify that there is a chance that there
is no unique Fielld such as AU_ID that you can do comparison to do the
count.
what i'm trying to do is to come up programmatically in SQL with a
standard way to add a counter (Unique Integer Value) to a resultset
with any number of tables and rows.

Is there a way to achieve this in the absence of a unique value such as
aui_ID in your example?

Thanks again.

Gent

[David Portas]

I would have to ask why you want to return the duplicate data in the
first place. I originally assumed that this was just for display on the
screen, in which case the client application is surely the best place
to solve the problem. Duplicate data in the database is bad news as I'm
sure you know and adding an arbitrary integer value obviously doesn't
solve the problem of redundancy, except maybe in the case of an
intermediate "staging" table. In a staging table you would probably
want to use IDENTITY to generate the row numbers and then filter the
result based on some logical key.

In SQL 2000 there is no generic method to generate row numbers in a
query. In SQL Server 2005 we have the ROW_NUMBER function to do this.

--
David Portas
SQL Server MVP
--