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

Assigning categories to values 1 Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 2:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
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.
Post #1452437
Posted Tuesday, May 14, 2013 4:06 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 @ 8:49 AM
Points: 3,075, Visits: 3,188
Good one.
Thanks!
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1452480
Posted Tuesday, May 14, 2013 4:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 1,938, Visits: 2,374
Good question Hugo :)


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1452491
Posted Tuesday, May 14, 2013 4:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 1,029, Visits: 2,336
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.
Post #1452494
Posted Tuesday, May 14, 2013 4:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,404, Visits: 1,315
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
Post #1452496
Posted Tuesday, May 14, 2013 4:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,404, Visits: 1,315
Nice question, thank you very much for two very valuable points



See, understand, learn, try, use efficient
© Dr.Plch
Post #1452498
Posted Tuesday, May 14, 2013 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 1,029, Visits: 2,336
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


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1452503
Posted Tuesday, May 14, 2013 5:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:31 AM
Points: 1,890, Visits: 2,192
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
Post #1452504
Posted Tuesday, May 14, 2013 5:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:26 AM
Points: 1,802, Visits: 6,571
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!
Post #1452518
Posted Tuesday, May 14, 2013 6:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, 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 &gt;= @compare1 and @test &lt;= @compare2) OR (@test &gt;= @compare2 and @test &lt;= @compare1) then 'Sheesh, what a long way around.' end

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



Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Post #1452532
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse