adding identity column

I'm adding an Identity column to a table like this:
SELECT DISTINCT
T.NAME_LAST,
T.NAME_FIRST,
T.DOB,
RowID = IDENTITY(INT, 1, 1)
INTO #TempResultSet
FROM #TempPopulation T
LEFT JOIN #TempDateOfService B
ON B.KEY_ID = T.KEY_ID
ORDER BY NAME_LAST, NAME_FIRST
The problem I'm having is that the RowID isn't added to the table after it's
Ordered, which is what I want/need. Is there a way to do this, without
having to put the data into another temp table, where it's already ordered?
Thanks, AndreSELECT INTO does not guarantee order.
See this KB article:
http://support.microsoft.com/kb/273586
And yesterday's thread in this group:
"Row numbering unpredictable"
If you know the order by is NAME_LAST, NAME_FIRST then what difference does
it make what the identity value is? Never mind that if someone changes
their last name, suddenly your identity values are all messed up.
"Andre" <no@.spam.com> wrote in message
news:OGoht0oXGHA.3740@.TK2MSFTNGP03.phx.gbl...
> I'm adding an Identity column to a table like this:
> SELECT DISTINCT
> T.NAME_LAST,
> T.NAME_FIRST,
> T.DOB,
> RowID = IDENTITY(INT, 1, 1)
> INTO #TempResultSet
> FROM #TempPopulation T
> LEFT JOIN #TempDateOfService B
> ON B.KEY_ID = T.KEY_ID
> ORDER BY NAME_LAST, NAME_FIRST
> The problem I'm having is that the RowID isn't added to the table after
> it's
> Ordered, which is what I want/need. Is there a way to do this, without
> having to put the data into another temp table, where it's already
> ordered?
> Thanks, Andre
>|||Thanks Aaron. The reason I need the identity column in the order of
name_last, name_first is because I'm using the identity column in a sproc
that handles paging. I get my resultset, in this case it's 2000 records,
and I only want to return 25 records to the front-end. I need my data
ordered so I know which 25 to send in. I've got it working by putting my
ordered data into a temp table, then into another temp where I add the ident
column. It just seemed like an extra step, and an extra temp table.
Perhaps not the most optimal solution but it works.
Andre|||> Thanks Aaron. The reason I need the identity column in the order of
> name_last, name_first is because I'm using the identity column in a sproc
> that handles paging.
THERE ARE BETTER SOLUTIONS THAN THIS!
http://www.aspfaq.com/2120|||I'm basically doing exactly what the article showed, but just having to make
use of the temp table to order my data. My method is precisely what I've
seen in SQL Magazine previously.
Thanks Aaron.
Andre