SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SOME


SOME

Author
Message
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3889 Visits: 2256
Igor, congratulations on finding an easy way to pad your score! :-)

Steve, Thanks for the question.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19229 Visits: 12426
Nice question on a little known (and, frankly, barely useful) feature.
Too bad it is hampered by the all too common mistake of depending on a time format. In some regions of the world, the test table has data for January 1st, February 1st, March 1st, and May 1st.
#FeelingLikeBrokenRecord


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
venoym
venoym
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 Visits: 2082
Hugo Kornelis (3/21/2012)
Nice question on a little known (and, frankly, barely useful) feature.
Too bad it is hampered by the all too common mistake of depending on a time format. In some regions of the world, the test table has data for January 1st, February 1st, March 1st, and May 1st.
#FeelingLikeBrokenRecord


Hugo, According to my reading... this question doesn't care which date format (other than the answer that specifies Jan 3rd) it is using. In most, if not all, regions I would suppose that it would return all dates.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19229 Visits: 12426
venoym (3/21/2012)
Hugo Kornelis (3/21/2012)
Nice question on a little known (and, frankly, barely useful) feature.
Too bad it is hampered by the all too common mistake of depending on a time format. In some regions of the world, the test table has data for January 1st, February 1st, March 1st, and May 1st.
#FeelingLikeBrokenRecord


Hugo, According to my reading... this question doesn't care which date format (other than the answer that specifies Jan 3rd) it is using. In most, if not all, regions I would suppose that it would return all dates.

True; for those who blindly copy and paste the code, it will not make a difference. To those who try to work out the answer in their head, it may make a difference if they see the "Jan 3" in the text of the question.

Until I discovered the "incorrect" use of ANY and realised that this is what the question is about, I suspected that the question might be a trick question on date formats.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6677 Visits: 2399
I had only looked at the ANY and SOME syntax once before and did not see a need for them. They seem to perform exactly like the IN statement unless I am missing something. Are there any instances where you could not perform the same logic using IN instead of ANY?
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60347 Visits: 9730
KWymore (3/21/2012)
I had only looked at the ANY and SOME syntax once before and did not see a need for them. They seem to perform exactly like the IN statement unless I am missing something. Are there any instances where you could not perform the same logic using IN instead of ANY?


You can use inequality comparisons with Any/Some. For example:

SELECT  *
FROM ( VALUES ( 1), ( 2), ( 3) ) AS MyQuery (MyCol)
WHERE MyCol > ANY ( SELECT MyCol2
FROM ( VALUES ( 2), ( 3) ) AS MyQuery2 (MyCol2) ) ;



Note the use of ">ANY". IN will only do equality comparisons.

- 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
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19229 Visits: 12426
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?

Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Gobikannan
Gobikannan
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2304 Visits: 614
Nice question.

-----------------
Gobikannan
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60347 Visits: 9730
Hugo Kornelis (3/21/2012)
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?

Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.


There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.

- 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
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6677 Visits: 2399
GSquared (3/21/2012)
Hugo Kornelis (3/21/2012)
KWymore (3/21/2012)
Are there any instances where you could not perform the same logic using IN instead of ANY?

Yes, as GSquared already shows.
However, there are (as far as I know) no cases where an ANY or SOME could not be replaced by an equivalent EXISTS subquery.


There are always alternatives to any particular query syntax. These options (Any/Some/All) can make a query significantly more readable than some complex Exists subqueries, in the right cases.

I haven't tested them for performance, nor have I used them with any frequency, so can't speak to that point, but a QotD that highlights a less-known feature for possible use, is perfectly valid.


I would end up using Exists/Not Exists for these scenarios. However, I do see your point on readability as I have written some fairly complex, ugly Exists statements before. I will have to keep ANY/ALL in mind then for these scenarios.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search