Posted by aj on 10/16/07 14:22

Are you on SQL Server 2005? Have a look at the ROW_NUMBER() function.

ala:
SELECT ROW_NUMBER() OVER (ORDER BY <some column>) AS ROW,
col,col,col,col FROM <table>

Combine this w/ a group by and potentially a CTE, and you /might/
get what you're looking for. Plus its ANSI to boot.

PS - I'm from an Informix background also. I happen to think its a
very useful database engine.

hth

Allen Jantzen

Tim wrote:
> Hi Folks,
>
> I come from an Informix background, (pauses for the laughter to die
> down), where SQL can access a hidden attribute, (ROWID), that uniquely
> identifies each record in a table at that point in time. It's similar
> to an IDENTITY attribute but it is not visible unless specifically
> selected and the RDBMS actively re-uses the numbers as they become
> vacant through deletion of records.
>
> However it can be very useful if you are trying to unscramble static
> but erroneously duplicate data rows built up due to an 'undocumented
> feature' of the application software. This is the situation I find
> myself in at the moment, so I was wondering if there was anything of a
> similar nature hidden or undocumented in SQL Server?
>
> I realise I could make a copy of the table and add an IDENTITY
> attribute, but I'm playing with some 75,000,000 rows of data that must
> all be salvaged, which complicates matters a little.
>
> Tia, Tim
>

[Back to original message]