Aggregate Function on Uniqueidentifier (GUID)

Question

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?

Solution 1

Assuming you're using SQL Server 2005 or later:

;with Numbered as (
     select category,guid,ROW_NUMBER() OVER (PARTITION BY category ORDER BY guid) rn
     from myTable
)
select * from Numbered where rn=1

Solution 2

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

Solution 3

Aggregate functions can be used on Uniqueidentifier columns if SQL Server Version >= 2012

expression

Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MIN can be used with numeric, char, varchar, uniqueidentifier, or datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.

Solution 4

declare @T table(category char(1), guid uniqueidentifier) 

insert into @T 
select 'a', newid() union all
select 'a', newid() union all
select 'b', newid()

select
  S.category,
  S.guid
from
(  
  select
    T.category,
    T.guid,
    row_number() over(partition by T.category order by (select 1)) as rn
  from @T as T
) as S
where S.rn = 1

If you are on SQL Server 2000 you could to this

select 
  T1.category,
  (select top 1 T2.guid 
   from @T as T2
   where T1.category = T2.category) as guid
from @T as T1
group by T1.category   

View more solutions

Comments

[verdesmarald]

Why are you doing this? Can you just use SELECT DISTINCT category FROM myTable instead? Or do you really need an arbitrary GUID for each category?

[Can Sahin]

@veredesmarald: Yes, I need an arbitrary GUID for each category.

[J Webb]

you could convert it to a string - MIN(CAST(guid AS VARCHAR(36)))

[StuartLC]

+1 - Link to MIN in 2008 R2 vs 2012

[Can Sahin]

Thanks for the effort, but your answer is incorrect: TOP 1 will return only 1 record after grouping (i.e., the answer will contain only either A or B).