Assigning categories to values 1

• Alex Fekken (5/18/2013)

But then, I am not a native English speaker, so what do I know?

I probably should have confessed earlier that neither is mine. But judging by your last name Hugo, it is probably the same as yours (dutch).

Yup, you guessed right - I'm Dutch too.

(I hope this discussion isn't undermining future questions that Hugo has planned; I see there is a '1' at the end of the question title)

No worries. Number 2 has already been piblished and doesn't use BETWEEN. No #3 planned at this time.

(And even if I had planned another question that involves BETWEEN, I'd still not worry about it. The majority of QotD responders never visit the forums, so there's still value in having a question that covers something already beaten to death on the forums).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• Alex Fekken (5/18/2013)[hr.When I said I would exclude the endpoints I was thinking of the continuous case, because it is the more general one (e.g. pick a number between 0 and 1).

In the continuous case you think it intuitive to assume the interval is open. I tend to think the opposite, because I think that "between a and b" refers to a 1-ball (or a 2-ball, when a and b are positions on a plane, and so on); a ball in a Euclidean space is compact (under the induced topology) and there's no reason for the definition of a ball in 1-dimensional Euclidean space to be different from the definition in higher dimensions, and of those four intervals only the one including both endpoints is compact. But usually in the continuous case I write the interval as for example (0,1), (0,1], [0,1) or [0,1] depending on which endpoint(s) I intend to include, and it would be nice if SQL could have a similar simple notation instead of "between". For ranges (discrete case), I use 1..9 instead of [1,9] and 2..8 instead of (1,9) so I feel strongly that the notation suggests that named bounds are included in the range case. So my mathematical background tells me to include endpoints.

I think the best way of dealing with ranges (which is what I think BETWEEN is trying to do) would be in the way SQL partition functions are defined because there are often multiple contiguous ranges to deal with. The moment you try the single out a single one of them you've got an issue with (or at least decisions to make about) the endpoints. Hugo avoided this by using integers, but coming from a mathematical background I prefer approaches and solutions that also work in, and are (or can be seen as) intuitive from, a more general perspective.

Yes, a nice way of indicating what to do about endpoints without reverting to direct use of the conjunction of two predicates would be preferable to "between", at least for those of us who are used to using notations that don't directly represent words (and I guess others could get used to it over time).

Tom

• This could go on for a long time... 🙂

a ball in a Euclidean space is compact (under the induced topology) and there's no reason for the definition of a ball in 1-dimensional Euclidean space to be different from the definition in higher dimensions

The balls are no longer compact in infinite dimensional spaces (where many interesting applications, e.g. quantum mechanics, are) unless you use very special (weak) topologies. Compact sets are important precisely because they are so special, not general. So I don't think compactness is a good argument. Much of function theory is done on open sets to avoid having to deal with boundaries, which is probably where my preference for open sets comes from. But I do agree with you notation for discrete sets (which are always both open and closed under the discrete topology anyway).

I think (and hope) that I have made my point, which is not that my intuition is "better" than yours, just that you could argue either way, depending on context and/or background.

• Alex Fekken (5/19/2013)

This could go on for a long time... 🙂

yes, it could. :w00t:

The balls are no longer compact in infinite dimensional spaces (where many interesting applications, e.g. quantum mechanics, are) unless you use very special (weak) topologies.

we maybe don't agree on what topologies are "weak". (closed) Balls in an infinite-dimensional complete metric space are compact. Balls in an incomplete metric space are not (even in a simple 1-dimensional case like the rational line, balls are not compact). I haven't looked at the topologies used in QM but I suspect it will be lack of completeness rather than infinite dimension that makes balls incompact. Of course Borel's proof of compactness of totally bounded closed sets in Euclidean space breaks down with infinite dimensions, and I think that even adding completeness in leaves one needing something analogous to the Lowenheim-Skolem theorem or Tychonoff's theorem (that might be the best starting point) - or maybe a weak version of the axiom of choice would be enough (anyway I'm confused as to whether Tychonoff's theorem implies AC or not - I think it doesn't, but I'm not sure, and all my maths books are in the UK, four hours flight away).

I think (and hope) that I have made my point, which is not that my intuition is "better" than yours, just that you could argue either way, depending on context and/or background.

Oh, yes, quite definitely - it is a matter of how one reacts to "between" and there's nothing that would make everyone react the same way. My point really was that there's nothing inherently counter-intuitive in inclusion of bounds, and I didn't want to suggest that there would be something inherently counter-intuitive in their exclusion (although in the discrete case there would be for me, there might not be for something else).

Tom

• You made me doubt myself for a moment, but:

http://planetmath.org/compactnessofclosedunitballinnormedspaces

[cool math]Pretty sure the same is true of any metric space: cover the ball with open neighbourhoods of the endpoints of a basis of unit vectors where each such neighbourhood covers only one such endpoint, as well as any open sets that you need to cover the rest of the ball but that do not cover those endpoints. If you have an infinite number of dimensions then you won't be able to refine the cover to a finite one because each endpoint is covered by only one open set.[/cool math]

Thanks for an interesting discussion. We should probably continue it somewhere else if you want to, given the direction this is taking...

• Alex Fekken (5/19/2013)

You made me doubt myself for a moment, but:

You are right. I should be more careful when speculating without references. AC implies that there are bounded infinite point sequences that have no limit points in any infinite-dimensional normed space, so it can't be compact. I wish I'd been aware of that 46 years ago!

http://planetmath.org/compactnessofclosedunitballinnormedspaces

[cool math]Pretty sure the same is true of any metric space: cover the ball with open neighbourhoods of the endpoints of a basis of unit vectors where each such neighbourhood covers only one such endpoint, as well as any open sets that you need to cover the rest of the ball but that do not cover those endpoints. If you have an infinite number of dimensions then you won't be able to refine the cover to a finite one because each endpoint is covered by only one open set.[/cool math]

That probably works for infinite dimensional metric vector spaces even if the metric isn't induced by a homogenous norm, so if you have the definition of dimensionality which requires a vector space it's fine. I don't recall whether that definition of dimensionality was generally accepted though, there used to be many other definitions, several of which didn't even require a metric, let alone a coordinate system. I'm not sure whether it works for non-vector space metric spaces (if we use a definition that allows them to have infinite dimensionality, and maybe a field other than the reals for the distance field). That's probably too difficult question for my (extremely rusty) knowledge of topology.

Tom

• Good question. I'd hope I'd never write BETWEEN -1 AND -10 but it was easy to miss as an error/anomaly in someone else's code.

• It's a shame that "Between -1 And -10" and "Between -10 and -1" aren't equivalent in T-SQL.

I haven't read BOL re. "Between" but does it state that it has to be Lower to Higher ?

If it was an Exam Question then I would've accepted both Answers.

David

• David Conn (5/23/2013)

It's a shame that "Between -1 And -10" and "Between -10 and -1" aren't equivalent in T-SQL.

I haven't read BOL re. "Between" but does it state that it has to be Lower to Higher ?

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

If it was an Exam Question then I would've accepted both Answers.

If the exam question was on English, I would agree. For an SQL exam, I would not. Examiners are supposed to test whether the candidate understands the subject matter as it is, not as the examiner would like it to be.

For an exam with open (not multiple choice) questions, if BETWEEN was used in an answer with begin and end parameters reversed but otherwise correct, I would only apply a small deduction.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• David Conn (5/23/2013)

It's a shame that "Between -1 And -10" and "Between -10 and -1" aren't equivalent in T-SQL.

David

More to the point, it's a shame you don't get an error when BETWEEN <higher> AND <lower> is encountered.

• Thanks Hugo.

I've read the BOL entry now and it is clear about order.

Regards

David

• Hugo Kornelis (5/18/2013)

not native but, if I may say so, better than the average non-native English speaker

Better than most native English speakers, unfortunately...

Viewing 12 posts - 61 through 71 (of 71 total)