February 18th, 2003, 12:25 PM
Generating a unique ID for each DISTINCT row
Is it possible in SQL (specifically SQL Server stored procedures) to return any kind of unique value along with each row in a SELECT DISTINCT... recordset?
I don't care what the value is, as long as there's one for each row in the recordset and each one is unique.
February 18th, 2003, 12:46 PM
One way to do this in your stored proc is to create a temporary table in your stored proc and make one column as an identity column. Then you can insert rows into the temp table and then select from it.
Another way to do this:
-- Create the temp table
CREATE TABLE #temp (
id INTEGER IDENTITY(1,1),
-- Insert rows into the temp table
INSERT INTO #temp (
-- Return the rows that were selected into the temp table
SELECT * FROM #temp
-- Drop the temp table before quitting the stored proc.
DROP TABLE #temp
SELECT DISTINCT newid(), rest of columns
The newid() function will return unique GUIDs. Depending on what you want, you can use either way.