ANY

  • Comments posted to this topic are about the item ANY

  • I wonder if this one was to makeup the previous QotD:laugh:

  • 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 ?

  • 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...:)

  • No it is clear...

    Thanks for the clarification 🙂

  • 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, suus solum profundum variat

  • 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 😀

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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 😀

    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, suus solum profundum variat

  • Excellent questions ..............:)

  • 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 testif 'Value1' < any (select 'Value1')

    select 1

    else

    select 0returns '0'. The code in the question returns '1' becauseif 'Value1' < any (select 'value2'

    union all select 'value3'

    union all select 'value4')

    select 1

    else

    select 0 returns '1'.:D

    Derek

  • Very cool. I wasn't aware of the Any/Some/All operators in SQL. Didn't even know they existed. Learned something from this one.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I found the reason given lacking as well and was thinking along the lines of the other comments regarding case.

    Did learn about the ANY clause though.

  • Steve thanks for an eye opening question. Never knew nor would I ever find out about the "some / any" operators.

    Now answer the following question:

    How many hours have you spent memorizing BOL and MSDN pages for T-SQL in revision 2005?

    (Worth hundreds of points)

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))

    DROP TABLE [dbo].[Table_1]

    /****** Object: Table [dbo].[Table_1] Script Date: 06/20/2008 08:46:22 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Table_1](

    [column1] [nchar](10) NULL,

    [column2] [nchar](10) NULL,

    [column3] [nchar](10) NULL,

    [column4] [nchar](10) NULL

    ) ON [PRIMARY]

    INSERT [Table_1]

    ([column1], [column2], [column3], [column4])

    VALUES

    ('Value1', 'Value2', 'Value3', 'Value4')

    IF 'Value1' < ANY ( SELECT [column1] FROM [Table_1] )

    SELECT '1' TEST1

    ELSE

    SELECT '0' TEST1;

    IF 'Value4' < ANY ( SELECT [column1] FROM [Table_1] )

    SELECT '1' TEST2

    ELSE

    SELECT '0' TEST2;

    IF 'Value4' = ANY ( SELECT [column1] FROM [Table_1] )

    SELECT '1' TEST3

    ELSE

    SELECT '0' TEST3;

    IF 'Value1' = ANY ( SELECT [column1] FROM [Table_1] )

    SELECT '1' TEST4

    ELSE

    SELECT '0' TEST4;

    ONly Test4 returns TRUE!

    Please refund us our 2 points!!! 😀

    Yama Kamyar

  • The query shows "value1", not "Value1", which definitely depends on case sensitivity.

    however, in any case, "value1" is less than "value2" and the others. Your example bends the rows into columns. This is what the question asked:

    /****** Object: Table [dbo].[Table_1] Script Date: 06/20/2008 08:46:22 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Table_1](

    [column1] [nchar](10) NULL

    ) ON [PRIMARY]

    INSERT [Table_1] ([column1]) VALUES ('Value1')

    INSERT [Table_1] ([column1]) VALUES ('Value2')

    INSERT [Table_1] ([column1]) VALUES ('Value3')

    INSERT [Table_1] ([column1]) VALUES ('Value4')

    select * from Table_1

    IF 'Value1' < ANY ( SELECT [column1] FROM [Table_1] )

    SELECT '1' TEST1

    ELSE

    SELECT '0' TEST1;

    Drop TABLE [dbo].[Table_1]

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply