#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    29
    Rep Power
    0

    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.

    Thanks!
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,643
    Rep Power
    4248
    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.
    Code:
    -- Create the temp table
    CREATE TABLE #temp (
         id  INTEGER IDENTITY(1,1),
         col1 type1,
         col2 type2,
         ....
    )
    
    -- Insert rows into the temp table
    INSERT INTO #temp (
          col1,
          col2,
          ...
    )
    SELECT DISTINCT 
    ...
    ...
    
    -- 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
    Another way to do this:
    SELECT DISTINCT newid(), rest of columns

    The newid() function will return unique GUIDs. Depending on what you want, you can use either way.

IMN logo majestic logo threadwatch logo seochat tools logo