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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11000 Visits: 11991
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14302 Visits: 12197
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

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
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.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14302 Visits: 12197
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

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
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...
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14302 Visits: 12197
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

marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 319
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.
David Conn
David Conn
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: 3149 Visits: 1148
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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11000 Visits: 11991
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
marlon.seton
marlon.seton
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 319
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.
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