Prev: DBTYP.NET Studio 2010 - Database Comparison Suite Released
Next: transaction history for a table
From: Plamen Ratchev on 10 Mar 2010 23:23
DWalker07 wrote:
> Declare @InvalidId As NVarchar(40)
> Set @InvalidId = (Select Min(SomeId)
> From SomeTable
> Group By SomeOtherColumn
> Having Count(Distinct ThirdColumn) > 1)
>
> But I can't do this! (I know how to accomplish what I need, but it's a
> much longer statement.)

There is no need for much longer statement, just cast the GUID column to VARCHAR:

SET @InvalidId = (SELECT MIN(CAST(SomeId AS VARCHAR(36)))
FROM SomeTable
GROUP BY SomeOtherColumn
HAVING COUNT(DISTINCT ThirdColumn) > 1);

--
Plamen Ratchev
http://www.SQLStudio.com
From: DWalker07 on 11 Mar 2010 11:12
Plamen Ratchev <Plamen(a)SQLStudio.com> wrote in
news:jI-dnX5r9sTV7QXWnZ2dnUVZ_usAAAAA(a)speakeasy.net:

> DWalker07 wrote:
>> Declare @InvalidId As NVarchar(40)
>> Set @InvalidId = (Select Min(SomeId)
>> From SomeTable
>> Group By SomeOtherColumn
>> Having Count(Distinct ThirdColumn) > 1)
>>
>> But I can't do this! (I know how to accomplish what I need, but it's
>> a much longer statement.)
>
> There is no need for much longer statement, just cast the GUID column
> to VARCHAR:
>
> SET @InvalidId = (SELECT MIN(CAST(SomeId AS VARCHAR(36)))
> FROM SomeTable
> GROUP BY SomeOtherColumn
> HAVING COUNT(DISTINCT ThirdColumn) > 1);
>

Yes, I know I can cast and then get the min, and then cast back to a GUID
if I need to.

BUT the main question remains: If you can order by a UniqueIdentifier, why
can't you find the MIN or MAX of a column of UniqueIdentifiers? It is not
consistent. Either UniqueIdentifiers can be placed in order, or they
can't. Right?


David Walker
From: Plamen Ratchev on 11 Mar 2010 11:51
DWalker07 wrote:
> BUT the main question remains: If you can order by a UniqueIdentifier, why
> can't you find the MIN or MAX of a column of UniqueIdentifiers? It is not
> consistent. Either UniqueIdentifiers can be placed in order, or they
> can't. Right?
>
>
> David Walker
This is simply the way MIN/MAX are implemented. Per BOL:

"MIN can be used with numeric, char, varchar, or datetime columns..."

In my opinion it really does not make sense to use MIN/MAX with UNIQUEIDENTIFIER (as I do not even know what the meaning
of MIN/MAX GUID value is). By nature using it in ORDER BY has always been a solution to provide random rows, not a
meaningful ordering. But if you feel that this is something that should be implemented, then you can submit your suggestion:
https://connect.microsoft.com/SQLServer/

There are many things that are not consistent in T-SQL, some for historic reasons some not. Not sure all are worth
changing, there are more important features I rather see implemented in the product.

--
Plamen Ratchev
http://www.SQLStudio.com
From: DWalker07 on 12 Mar 2010 14:57
Plamen Ratchev <Plamen(a)SQLStudio.com> wrote in
news:jI-dnXRr9sQ-ggTWnZ2dnUVZ_usAAAAA(a)speakeasy.net:

> DWalker07 wrote:
>> BUT the main question remains: If you can order by a
>> UniqueIdentifier, why can't you find the MIN or MAX of a column of
>> UniqueIdentifiers? It is not consistent. Either UniqueIdentifiers
>> can be placed in order, or they can't. Right?
>>
>>
>> David Walker
> This is simply the way MIN/MAX are implemented. Per BOL:
>
> "MIN can be used with numeric, char, varchar, or datetime columns..."
>
> In my opinion it really does not make sense to use MIN/MAX with
> UNIQUEIDENTIFIER (as I do not even know what the meaning of MIN/MAX
> GUID value is). By nature using it in ORDER BY has always been a
> solution to provide random rows, not a meaningful ordering. But if you
> feel that this is something that should be implemented, then you can
> submit your suggestion: https://connect.microsoft.com/SQLServer/
>
> There are many things that are not consistent in T-SQL, some for
> historic reasons some not. Not sure all are worth changing, there are
> more important features I rather see implemented in the product.
>

Yes, normally a Min or Max GUID would not make much sense. But that's the
reason I gave my example earlier, which you replied to with the solution
using Cast. I sometimes want ANY random element of a column, and it's
generally only for error or tracking purposes. Any record is as good as
any other. And sometimes I know that all records in the subset have the
same value, so I just want to pick one of them. (An aggregate function
that returns FIRST like some databases have, or something that returns ANY
element of the column would be just as good.)

David Walker
 | 
Pages: 1
Prev: DBTYP.NET Studio 2010 - Database Comparison Suite Released
Next: transaction history for a table