Aggregate Function on Uniqueidentifier (GUID)

sql sql-server guid aggregate uniqueidentifier
Heinzi picture Heinzi · May 20, 2011 · Viewed 27.3k times · Source

Let's say I have the following table:

category | guid
---------+-----------------------
   A     | 5BC2...
   A     | 6A1C...
   B     | 92A2...

Basically, I want to do the following SQL:

SELECT category, MIN(guid)
  FROM myTable
 GROUP BY category

It doesn't necessarily have to be MIN. I just want to return one GUID of each category. I don't care which one. Unfortunately, SQL Server does not allow MIN or MAX on GUIDs.

Of course, I could convert the guid into a varchar, or create some nested TOP 1 SQL, but that seems like an ugly workaround. Is there some elegant solution that I've missed?

Answer

K Biel picture K Biel · Oct 27, 2011

Just cast it as a BINARY(16).

SELECT category, MIN(CAST(guid AS BINARY(16)))
FROM myTable
GROUP BY category

You can cast it back later if necessary.

WITH CategoryValue
AS
(    
    SELECT category, MIN(CAST(guid AS BINARY(16)))
    FROM myTable
    GROUP BY category
)
SELECT category, CAST(guid AS UNIQUEIDENTIFIER)
FROM CategoryValue

Related questions

SCOPE_IDENTITY() for GUIDs?

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs …

Read More
Convert GUID to varchar(32)

How can I convert a GUID which is 36 characters to a VARCHAR(32)? I'm trying to copy data from one table to another. There are two similar columns from these two tables. Table1.colx is a GUID so it is 36 characters …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More