June 19, 2008 at 10:29 pm
I wonder if this one was to makeup the previous QotD:laugh:
June 19, 2008 at 11:09 pm
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 ?
June 20, 2008 at 1:54 am
Danasegarane.A (6/19/2008)
I am not able to understand this oneI 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...:)
June 20, 2008 at 2:36 am
No it is clear...
Thanks for the clarification 🙂
June 20, 2008 at 3:06 am
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
June 20, 2008 at 3:19 am
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! 😉
June 20, 2008 at 3:23 am
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
June 20, 2008 at 4:42 am
Excellent questions ..............:)
June 20, 2008 at 5:24 am
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
June 20, 2008 at 7:30 am
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
June 20, 2008 at 7:50 am
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.
June 20, 2008 at 9:00 am
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)
June 20, 2008 at 10:10 am
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
June 20, 2008 at 10:24 am
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