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: Monday, November 24, 2014 10:03 AM
Points: 1,895, Visits: 2,194
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: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 6:22 AM
Points: 1,045, Visits: 1,774
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: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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: Today @ 12:20 PM
Points: 3,397, Visits: 2,023
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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: Tuesday, November 11, 2014 9:19 AM
Points: 1,959, Visits: 585
Nice question.

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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: Today @ 12:20 PM
Points: 3,397, Visits: 2,023
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