Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

ANY Expand / Collapse
Author
Message
Posted Thursday, June 19, 2008 8:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:34 AM
Points: 33,162, Visits: 15,295
Comments posted to this topic are about the item ANY






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #520310
Posted Thursday, June 19, 2008 10:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 1,252, Visits: 63
I wonder if this one was to makeup the previous QotD
Post #520320
Posted Thursday, June 19, 2008 11:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 12, 2010 4:02 AM
Points: 41, Visits: 562
I am not able to understand this one

I run this query

if 'BLONP' < Any (select customerid from customers)
select '1'
else
select '0'

But it returns 1 only ?
Post #520337
Posted Friday, June 20, 2008 1:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 7:08 AM
Points: 469, Visits: 124
Danasegarane.A (6/19/2008)
I am not able to understand this one

I run this query

if 'BLONP' < Any (select customerid from customers)
select '1'
else
select '0'

But it returns 1 only ?


What's your customer table - I assume that the customerid column is some kind of char-based column for the above not to return you a conversion failure.

Try the following...

CREATE TABLE [dbo].[TimeGroup](
[column1] [nchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

Insert into the TimeGroup table values Value1, Value2, Value3, Value4

run the query as posted in the question and it will return 1 for any value in the if statement less than 'Value4'. Value4 is the highest value in you've added to column1, so here it will return 0. If you change the operator to =, it will return 1 for Value4.

i.e.
if 'Value1' < any ( select column1 from TimeGROUP )
select '1'
else
select '0'
Returns 1

if 'Value4' < any ( select column1 from TimeGROUP )
select '1'
else
select '0'

Returns 0

if 'Value4' = any ( select column1 from TimeGROUP )
select '1'
else
select '0'

Returns 1

and so on...:)

Post #520419
Posted Friday, June 20, 2008 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 12, 2010 4:02 AM
Points: 41, Visits: 562
No it is clear...


Thanks for the clarification :)
Post #520445
Posted Friday, June 20, 2008 3:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:53 AM
Points: 1,049, Visits: 3,003
Of course, strictly speaking the question will only be correct for case insensitive collations, since the values in the question are "value1, value2, value3 and value4", yet the comparison value is "Value1".

Nonetheless, a good question.


Semper in excretia, sumus solum profundum variat
Post #520463
Posted Friday, June 20, 2008 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 5,967, Visits: 8,219
majorbloodnock (6/20/2008)
Of course, strictly speaking the question will only be correct for case insensitive collations, since the values in the question are "value1, value2, value3 and value4", yet the comparison value is "Value1".

Nonetheless, a good question.


Strictly speaking, it's correct for case sensitive collations as well, since uppercase character sort before lowercase characters. (And in binary collations, ALL uppercase characters sort before ANY lowercase character). It would have been different if the table values were uppercase and the value in the query was lowercase.

However, even more strictly speaking, the result would have been an error if the database collation is case sensitive. After all, in a case sensitive collation, the tables TimeGroup and TimeGROUP are distinct :D

But I do agree with your final statement - a good question to test understanding of a very little known SQL operator. Thanks, Steve! ;)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #520473
Posted Friday, June 20, 2008 3:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:53 AM
Points: 1,049, Visits: 3,003
Hugo Kornelis (6/20/2008)
majorbloodnock (6/20/2008)
Of course, strictly speaking the question will only be correct for case insensitive collations, since the values in the question are "value1, value2, value3 and value4", yet the comparison value is "Value1".

Nonetheless, a good question.


Strictly speaking, it's correct for case sensitive collations as well, since uppercase character sort before lowercase characters. (And in binary collations, ALL uppercase characters sort before ANY lowercase character). It would have been different if the table values were uppercase and the value in the query was lowercase.

However, even more strictly speaking, the result would have been an error if the database collation is case sensitive. After all, in a case sensitive collation, the tables TimeGroup and TimeGROUP are distinct :D

But I do agree with your final statement - a good question to test understanding of a very little known SQL operator. Thanks, Steve! ;)


Oops. My mistake. Thanks, Hugo.


Semper in excretia, sumus solum profundum variat
Post #520474
Posted Friday, June 20, 2008 4:42 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:26 AM
Points: 5,335, Visits: 1,383
Excellent questions ..............:)


Post #520513
Posted Friday, June 20, 2008 5:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
I think the explanation of the answer is wrong. It says
If any value meets these criteria, then TRUE is returned to the IF statement and it is true in this case since "VALUE1" was given in the question.
In fact, I beleive it returns true because the values "VALUE2", "VALUE3" and "VALUE4" were given in the question!
The test
if 'Value1' < any (select 'Value1')
select 1
else
select 0

returns '0'. The code in the question returns '1' because
if 'Value1' < any (select 'value2' 
union all select 'value3'
union all select 'value4')
select 1
else
select 0

returns '1'.:D


Derek
Post #520541
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse