ANY

Question

ANY

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

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

Yama,

you have changed the question!!

It's not a table with 4 columns, it's a table with one column 'column1' with 4 values 'value1','value2','value3','value4'

Kev

[whoops - beaten to it by Steve!!]

Pat Reiter Old Hand Points: 378 More actions · Answer 2

Drat! I thought that it was a case-sensitivity trick question, but couldn't remember which case sorts to the top. (And it's cheating to research anything for the QOD, right?)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Upper case before lower case (http://www.asciitable.com/), remember that we only had upper case way back when.

But do the QOD however you want. Some people want the points, maybe it's a resume bullet, so they'll research or run test code. Some guess to see if they know or if they can make an educated guess.

All's fair in how you do it. It should mean something to you if you participate, whatever that is (learning tool, quiz, indicative of knowledge, etc.).

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 4

I have a different result:

--Assuming that I have the following values in the TimeGroup table (value1, value2, value3, value4), what does this query return?

CREATE TABLE #TimeGroup(column1 VARCHAR(50),column2 VARCHAR(50),column3 VARCHAR(50),column4 VARCHAR(50))

INSERT INTO #TimeGroup(column1, column2,column3, column4) VALUES('value1','value2','value3','value4')

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

DROP TABLE #TimeGroup

Jamie

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736270

June 20, 2008 at 12:33 pm

#832010

The values are in rows, not columns.

Yama Kamyar SSC Enthusiast Points: 106 More actions · Answer 6

yea but the orignal question as I read it did specify column1, column2, column3, and column4 with respective values...

GSquared SSC Guru Points: 260824 More actions · Answer 7

Yama Kamyar (6/20/2008)
yea but the orignal question as I read it did specify column1, column2, column3, and column4 with respective values...

The whole point of "Any" is that it compares a value to a columnar set of values. The question wouldn't make any sense at all if the values were in a single row.

- 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

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 8

hey bitbucket, you may be as shocked as I was to learn that this is not even a SQL2005 addition. It existed in 2000! It's always humbling to learn something about a language that you've been been neck deep in for 8 years! (I didn't bother checking any further back)

brewmanz SSCommitted Points: 1575 More actions · Answer 9

GSquared (6/20/2008)
Yama Kamyar (6/20/2008)
yea but the orignal question as I read it did specify column1, column2, column3, and column4 with respective values...
The whole point of "Any" is that it compares a value to a columnar set of values. The question wouldn't make any sense at all if the values were in a single row.

Yama has a valid point (or should that be two points?) The question doesn't make sense as it is! It relies on the assumption that all the 'valueN' go in the same column. This is not stated, and so I can believe that Yama (and others) misunderstood the intent of the Q. I got this one right (I hope that they don't takes points away for arguing, even when you got the 'right' answer), but in the past, there have been trick questions which were deliberately misleading, with the answer saying (something like) "Wrong! You're assuming something, but that's not what I said!"

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

I can see your argument, but the question doesn't ever come close to specifying columns. It specifies values and shows a query on one column.

This is to learn and for fun. When I wrote it, I had no idea ANY existed either and struggled to get a good example. If you missed it, point taken that the question should say one column, but honestly, I think you interperted it badly.

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 11

The whole point of "Any" is that it compares a value to a columnar set of values. The question wouldn't make any sense at all if the values were in a single row.

I interpreted incorrectly that it was for rows but I have to agree the g-squared, point wasn't to get the answer correct, point was to understand how Any allows for columnar comparisons.

Jamie

GSquared SSC Guru Points: 260824 More actions · Answer 12

brewmanz (6/20/2008)
GSquared (6/20/2008)
Yama Kamyar (6/20/2008)
yea but the orignal question as I read it did specify column1, column2, column3, and column4 with respective values...
The whole point of "Any" is that it compares a value to a columnar set of values. The question wouldn't make any sense at all if the values were in a single row.
Yama has a valid point (or should that be two points?) The question doesn't make sense as it is! It relies on the assumption that all the 'valueN' go in the same column. This is not stated, and so I can believe that Yama (and others) misunderstood the intent of the Q. I got this one right (I hope that they don't takes points away for arguing, even when you got the 'right' answer), but in the past, there have been trick questions which were deliberately misleading, with the answer saying (something like) "Wrong! You're assuming something, but that's not what I said!"

I guess it's possible, if it had been a trick question, that it would have defined the data as columns instead of rows. But my basic philosophy on trick questions is that they aren't worth worrying about. They serve the purpose of making the author feel superior, and no other purpose at all. So, I answer the questions on the assumption that they are intended to make sense and to educate. Every now and again, trusting people that way gets me burned. But the vast majority of the time, it works.

It's just different approaches.

- 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

OCTom SSChampion Points: 11785 More actions · Answer 13

I love these types of questions. I care not if I get it right, I get to learn something and think about how I can apply it to my work.

Good one! 🙂

km.abbasi Grasshopper Points: 16 More actions · Answer 14

I am 100% sure that this question is wrong , there are two mistakes

1) value1, value2 and so on are in lower case and in search Value1 is initiated by capital letter.

2) if you run following query in adventure database it will return 1 and just 1 weather true or false

if '-' < any ( select Title from HumanResources.Employee )

select 1

else

select 0

Result 1

if 'Buyer' < any ( select Title from HumanResources.Employee )

select 1

else

select 0

Result 1

and actually this Statement would supposed to be like as followed

if 'Buyer' = any ( select Title from HumanResources.Employee )

select 1

else

select 0

Result 1

Because this value exist in Title Column of Employee table

if 'buyer' = any ( select Title from HumanResources.Employee )

select 1

else

select 0

Result 0 Because this value does not exist in Title Column of Employee table

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 15

km.abbasi (6/24/2008)
I am 100% sure that this question is wrong , there are two mistakes
1) value1, value2 and so on are in lower case and in search Value1 is initiated by capital letter.
2) if you run following query in adventure database it will return 1 and just 1 weather true or false
if '-' < any ( select Title from HumanResources.Employee )
select 1
else
select 0
Result 1
if 'Buyer' < any ( select Title from HumanResources.Employee )
select 1
else
select 0
Result 1
and actually this Statement would supposed to be like as followed
if 'Buyer' = any ( select Title from HumanResources.Employee )
select 1
else
select 0
Result 1
Because this value exist in Title Column of Employee table
if 'buyer' = any ( select Title from HumanResources.Employee )
select 1
else
select 0
Result 0 Because this value does not exist in Title Column of Employee table

The answer as given is correct.

1. The default setting for databases is case-insensitive so it doesn't matter it is 'value1' 'Value1' or even 'VaLuE1'!

2. If your Title column cas any value which is alphabetically greater than 'Buyer' then, yes, your first 3 selects will all return true and hence 1. If you can case-sensitivity on, the your final select with return false.

It is usually recommended that unless you have a very good reason, you don't turn case-sensitivity on. SQLserver will still store text as whatever case is entered, but will do the comparisons in a case independent way, which greatly simplifies most operations. For example, you don't have to remember if the job title was 'Project manager' or 'Project Manager'!

Derek