SOME rows

Question

SOME rows

Steve Jones - SSC Editor

SSC Guru

Points: 736162
More actions
January 28, 2015 at 8:15 pm

#72520

Comments posted to this topic are about the item SOME rows
January 29, 2015 at 1:27 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 33 total)

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

Thanks for question Steve.Never used this function so learnt new funtion 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Thank you for the post, Steve, very good one.

At first, I marked the"1" choice, but didn't feel it right, then thought it will be true for all rows as the comparison was been made with static string '2014' then changed to "8". 🙂 (I used it once so I vaguely remembered and had to refer msdn to make myself clear)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Great question. Aside from exam questions on MS certification, I have never ever encountered this in code and never used it myself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11365 More actions · Answer 4

Are there any circumstances where using SOME would be preferable to the equivalent with EXISTS?

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Never used Some fn b4, thanx for sharing the question & info 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Just encountered it - for the very first time in my career - in the generated query of a BO report.

When you select a parameter for a date (e.g. December 2014) it generated a WHERE clause like this:

WHERE month <= ANY (SELECT month FROM dbo.Calendar WHERE month = '2014-12')

I always wonder what kind of idiots where SQL generators for reporting tools.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

JaimePC SSC Eights! Points: 864 More actions · Answer 7

Nice QotD Steve!, the next question do it with clause "ANY"

😛

Brian Hibbert Ten Centuries Points: 1322 More actions · Answer 8

Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.

Jerry Beech Old Hand Points: 317 More actions · Answer 9

Good one Steve, I clicked the 1 and was about to submit when my eyes landed on the 500 and the light bulb clicked on..... 500 > 200 and = 2014 means Steve is being sneaky - as usual!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

I've never used it or encountered it either, so I learned something new. Thanks!

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 11

Good introduction Steve! I've never used the SOME function before.

I think it's also pertinent to point out that the reason all 8 rows returned was because of the use of a literal for the comparison and of course the literal didn't change for all eight rows. Had the literal been replaced with a column reference like this:

SELECT

CustomerID

FROM

sales

WHERE Salesyear = SOME (SELECT salesyear FROM sales WHERE amount > 200);

Then it would have resulted in only four rows returned (for those matching the Salesyear 2014).

I must admit some curiosity along with several others in wondering what benefits this presents over the EXISTS clause.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

doug.davidson SSCommitted Points: 1662 More actions · Answer 12

Brian Hibbert (1/29/2015)
Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.

So this is the real answer. Much better than the provided link in explaining why 8 was the correct answer.

Brian Hibbert Ten Centuries Points: 1322 More actions · Answer 13

doug.davidson (1/29/2015)
Brian Hibbert (1/29/2015)
Great question, and I nearly clicked 0 because '2014' = True SHOULD fail, but doesn't in SQL.
So this is the real answer. Much better than the provided link in explaining why 8 was the correct answer.

I think I'm not quite understanding what SOME (or Any) actually does.

A quick read of the BOL says the return type is Boolean, but it's not quite as simple as that. SOME isn't a standalone function as I initially thought, it's more like a modifier to the comparison operators.