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»»»

SOME Expand / Collapse
Author
Message
Posted Wednesday, March 21, 2012 5:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,625, Visits: 2,034
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
Post #1270100
Posted Wednesday, March 21, 2012 6:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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
Post #1270118
Posted Wednesday, March 21, 2012 6:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:15 AM
Points: 934, Visits: 1,526
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.
Post #1270128
Posted Wednesday, March 21, 2012 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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
Post #1270142
Posted Wednesday, March 21, 2012 8:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
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?
Post #1270182
Posted Wednesday, March 21, 2012 8:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1270196
Posted Wednesday, March 21, 2012 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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
Post #1270204
Posted Wednesday, March 21, 2012 8:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 1,719, Visits: 552
Nice question.

-----------------
Gobikannan
Post #1270205
Posted Wednesday, March 21, 2012 8:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1270211
Posted Wednesday, March 21, 2012 8:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
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.
Post #1270234
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse