Let's say we have this:
Country Capital
------------- -----------------
USA Washington D.C.
Ireland Dublin
Spain Madrid
How do we write a query without using a #temp table to do this:
ID Country Capital
-------- ------------- -----------------
1 USA Washington D.C.
2 Ireland Dublin
3 Spain Madrid
The ID column should be a new column that's made on the fly, there isn't an ID column in any of my tables. Each row in the ID column should increment by one.
Thanks in advance!
-Chris