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?

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? — verdesmarald, May 20 '11 at 08:38
@veredesmarald: Yes, I need an arbitrary GUID for each category. — Heinzi, May 20 '11 at 08:41

score 50 · Answer 1 · edited Mar 03 '15 at 11:10

50

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

edited Mar 03 '15 at 11:10

bacar

9,761
11
55
75

answered Oct 27 '11 at 21:04

K Biel

501
4
2

6

you could convert it to a string - `MIN(CAST(guid AS VARCHAR(36)))` – J Webb Jun 01 '13 at 15:39

score 31 · Accepted Answer · answered May 20 '11 at 08:40

31

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

answered May 20 '11 at 08:40

Damien_The_Unbeliever

234,701
27
340
448

score 19 · Answer 3 · answered Aug 26 '13 at 14:00

19

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.

answered Aug 26 '13 at 14:00

doerig

1,857
1
18
26

4

+1 - Link to `MIN` in [2008 R2](http://technet.microsoft.com/en-us/library/ms179916(v=sql.105).aspx) vs [2012](http://technet.microsoft.com/en-us/library/ms179916(v=sql.110).aspx) – StuartLC Feb 22 '14 at 06:24

score 4 · Answer 4 · answered May 20 '11 at 08:42

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

score -4 · Answer 5 · answered Nov 16 '14 at 09:20

-4

SELECT top 1 category, guid FROM myTable GROUP BY category,guid

answered Nov 16 '14 at 09:20

Abhi

55
1
10

1

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`). – Heinzi Nov 16 '14 at 09:26

Aggregate Function on Uniqueidentifier (GUID)

5 Answers5

Linked