SOME in T-SQL: how to use it?

Question

SELECT * FROM VNTREAT WHERE VN = SOME ('482')

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '482'.

Answer 1

See:

http://msdn.microsoft.com/en-us/library/ms175064.aspx

I'd never heard of it before now.

Answer 2

The correct syntax for some is to use a subquery.

select * from vntreat where vn = some(select '482')

In the case of hard-coded values you want to use just = or in:

--Get the rows where vn is '482':
select * from vntreate where vn = '482'

--Get any of the rows that have vn equal to '480', '482', or '485'
select * from vntreat where vn in ('480', '482', '485')

Answer 3

+1 Cool obscure SQL....Thanks curtisk...

From the links, it appears that SOME operates on series of returned values and can probably be expressed more understandable as 'AT LEAST ONE'... the usage looks similar to 'IN'...

Definitely cool...

Answer 4

Try SELECT * FROM VNTREAT WHERE VN = SOME (Select '482')

Answer 5

select custID from orders where regionID != "E" and discount > any (select discount from orders where regionID = "E" and discount > 3)

This query lists the customers who are getting discounts greater than any offered in the East region. The Any qualifier works just like the In except it allows for >, >=, <, <= as well as the = (In) and != (Not In) operators. Thus, in our example, the subquery generates a list of all discounts in the East greater than the standard 3% discount. We then compare all the other regions - regionID != "E" - to see if any of the discounts there are greater than ANY discount in the East. This tells our sale manager which customers are getting discounts in other parts of the country greater than any offered in the EAST where a special product promotion and discounts are being offered.