## Assigning categories to values 1

 Author Message Hugo Kornelis SSC-Dedicated Group: General Forum Members Points: 34606 Visits: 13122 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: http://sqlblog.com/blogs/hugo_kornelis Tom Thomson SSC Guru Group: General Forum Members Points: 51030 Visits: 13159 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 Mr or Mrs. 500 Group: General Forum Members Points: 583 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 dimensionsThe 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. Tom Thomson SSC Guru Group: General Forum Members Points: 51030 Visits: 13159 Alex Fekken (5/19/2013)This could go on for a long time... :-) yes, it could. 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 Mr or Mrs. 500 Group: General Forum Members Points: 583 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... Tom Thomson SSC Guru Group: General Forum Members Points: 51030 Visits: 13159 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 SSCommitted Group: General Forum Members Points: 1629 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 SSCarpal Tunnel Group: General Forum Members Points: 4112 Visits: 1247 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 SSC-Dedicated Group: General Forum Members Points: 34606 Visits: 13122 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.aspxIf 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: http://sqlblog.com/blogs/hugo_kornelis marlon.seton SSCommitted Group: General Forum Members Points: 1629 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.DavidMore to the point, it's a shame you don't get an error when BETWEEN AND is encountered.

## 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
 SQL Server 2017      SQL Server 2017 - Administration      SQL Server 2017 - Development SQL Server 2016      SQL Server 2016 - Administration      SQL Server 2016 - Development and T-SQL SQL Server 2014      Administration - SQL Server 2014      Development - SQL Server 2014 SQL Server 2012      SQL 2012 - General      SQL Server 2012 - T-SQL SQL Server vNext      SQL Server 15 - Administration      SQL Server 15 - Development SQL Server 2008      SQL Server 2008 - General      T-SQL (SS2K8)      June 2007 CTP      Working with Oracle      July CTP      SQL Server Newbies      Security (SS2K8)      SQL Server 2008 High Availability      SQL Server 2008 Administration      Data Corruption (SS2K8 / SS2K8 R2)      SQL Server 2008 Performance Tuning Cloud Computing      SQL Azure - Development      SQL Azure - Administration      Amazon AWS and other cloud vendors      General Cloud Computing Questions      CosmosDB      Azure Data Lake      Azure Machine Learning Reporting Services      Reporting Services      Reporting Services 2005 Administration      Reporting Services 2005 Development      Reporting Services 2008/R2 Administration      Reporting Services 2008 Development      SSRS 2012      SSRS 2014      SSRS 2016 Programming      Connecting      General      SMO/RMO/DMO      XML      Service Broker      Powershell      Testing      TFS/Data Dude/DBPro      SSDT      Continuous Integration, Deployment, and Delivery      R Services and R Language Data Warehousing      Integration Services      Strategies and Ideas      Analysis Services      Data Transformation Services (DTS)      Performance Point      Data Mining      PowerPivot      R language      Machine Learning Database Design      Disaster Recovery      Design Ideas and Questions      Relational Theory      Hardware      Virtualization      Security and Auditing SQLServerCentral.com      Anything that is NOT about SQL!      Contests!      Editorials      SQLServerCentral.com Announcements      SQLServerCentral.com Website Issues      Suggestions      Tag Issues with Content      Podcast Feedback      SQLServerCentral.com Test Forum      Articles Requested SQL Server 2005      Administering      Backups      Business Intelligence      CLR Integration and Programming.      Data Corruption      Development      Working with Oracle      SQL Server 2005 Compact Edition      SQL Server 2005 General Discussion      SQL Server 2005 Security      SQL Server 2005 Strategies      SS2K5 Replication      SQL Server Express      SQL Server 2005 Performance Tuning      SQL Server 2005 Integration Services      T-SQL (SS2K5)      SQL Server Newbies SQL Server 7,2000      Administration      Backups      Data Corruption      General      Globalization      In The Enterprise      Working with Oracle      Security      Strategies      SQL Server Newbies      Service Packs      SQL Server CE      Performance Tuning      Replication      Sarbanes-Oxley      T-SQL      SQL Server Agent SQL Server and other platforms      MySQL      Oracle      PostgreSQL      DB2      SQL Server and Sharepoint Older Versions of SQL (v6.5, v6.0, v4.2)      Older Versions of SQL (v6.5, v6.0, v4.2) Career      Certification      Employers and Employees      Events      Job Postings      Resumes and Job Hunters      Presentations and Speaking      Retired Members Testing Center      SQL Server Security Skills      Question of the Day (QOD) Microsoft Access      Microsoft Access Products and Books      Third Party Products         SQLCentric         Extreme Technologies.         Innovartis         Embarcadero         SQL Sentry         Sonasoft         Golden Gate Software         Idera         Lumigent         Red Gate Software         ApexSQL         Quest Software         CA      Discussions about Books         Discuss Programming Books          Discuss XML Books          Discuss T-SQL Books          Discuss Data Warehousing Books          Discuss DTS Books          Discuss SQL Server 2000 Books         Discuss SQL Server 7.0 Books Notification Services      Administration Article Discussions Future Versions      SQL 12