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


Assigning categories to values 1


Assigning categories to values 1

Author
Message
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 460
Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case.

Depends on where you are coming from: my initial thought, when I first encountered 'BETWEEN', was "I can't think why one would use BETWEEN in any practical case.".

BETWEEN suffers from at least two ambiguities until you consult the "official" definition to find out what arbitrary choices have been made to remove them.

The first is the issue in question: it would have made just as much sense, if not more, to make the definition symmetrical with respect to the two bounds. Because [1] it would be more work to write down the alternative expression and [2] the keyword AND in all other contexts implies symmetry of the operands, unlike e.g. 'x BETWEEN a TO b'.

The second ambiguity is whether BETWEEN includes or excludes the bounds. Intuitively I would make the wrong assumption there as well.

So for me it is a bad language feature, well-deserving of at least one Question of the Day.
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 5080
Good one.
Thanks!
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3073 Visits: 2766
Good question Hugo Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Raghavendra Mudugal
Raghavendra Mudugal
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 2958
Alex Fekken (5/14/2013)
Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case.

Depends on where you are coming from: my initial thought, when I first encountered 'BETWEEN', was "I can't think why one would use BETWEEN in any practical case.".

BETWEEN suffers from at least two ambiguities until you consult the "official" definition to find out what arbitrary choices have been made to remove them.

The first is the issue in question: it would have made just as much sense, if not more, to make the definition symmetrical with respect to the two bounds. Because [1] it would be more work to write down the alternative expression and [2] the keyword AND in all other contexts implies symmetry of the operands, unlike e.g. 'x BETWEEN a TO b'.

The second ambiguity is whether BETWEEN includes or excludes the bounds. Intuitively I would make the wrong assumption there as well.

So for me it is a bad language feature, well-deserving of at least one Question of the Day.


... even if AND is replaced with TO ... it still works in the same way... and when you know the BETWEEN included the bounds, why you want to use your "intuition" and then make the "wrong assumption"? (I have used this keyword and never thought of going alternate.. and BETWEEN works as it suppose to work)

(my concern is with the start expr with the higher value and then put lower value to the end expr and I don't see any practical case where and how it is used to make the code work...)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1937 Visits: 1323
Raghavendra Mudugal (5/14/2013)

(Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case. When I saw -1 as the start value.. I thought that might be the catch here and .... :-) )

I can think one can copy-paste positive thresholds and write minus signs afterwards.



See, understand, learn, try, use efficient
© Dr.Plch
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1937 Visits: 1323
Nice question, thank you very much for two very valuable points Hehe



See, understand, learn, try, use efficient
© Dr.Plch
Raghavendra Mudugal
Raghavendra Mudugal
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 2958
honza.mf (5/14/2013)
Raghavendra Mudugal (5/14/2013)

(Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case. When I saw -1 as the start value.. I thought that might be the catch here and .... :-) )

I can think one can copy-paste positive thresholds and write minus signs afterwards.


I see you missed the <joke></joke> tags Rolleyes

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 2255
Nicely posed and explained. I suspected the BETWEEN might cause a problem, but went for the wrong answer anyway.

I lost those two very valuable points, delaying my retirement by at least five years. Fortunately I have my mutual funds to fall back on. ;-)

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2766 Visits: 8083
Thomas Abraham (5/14/2013)
I lost those two very valuable points


I hadn't noticed it was two points.
Woo hoo - Barbados here I come!
Cool
srienstr
srienstr
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 433
declare @test int
set @test=-2
select case when @test between -1 and -10 then 'Order does not matter'
else 'Order matters' end as Conclusion


It would seem to me that order shouldn't matter. As it is, if you are using parameters for the comparison and don't know which will be larger, you would need the below:
select case when (@test >= @compare1 and @test <= @compare2) OR (@test >= @compare2 and @test <= @compare1) then 'Sheesh, what a long way around.' end



I saw it was from Hugo, and checked carefully for tricks. Not carefully enough. :-D


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
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