BETWEEN a rock AND a hard place
in [Queries]
|
From: cinnie on 29 Jan 2010 13:29 hello gurus This isn't a question as much as it is a beginner's comment on BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the conditions... 10 <= Value <= 20). But there are times, especially when working with intervals, when I might want non-overlapping values like, say... 10 < Value <= 20, then 20 < Value <= 30, etc. This is easy to do using... WHERE Value > 10 And Value <= 20 instead of BETWEEN...AND, but it gets harder when we don't know which of the limits is the lower bound and which is the upper bound. For example... SELECT tData.Country FROM tData WHERE tData.GDP BETWEEN (SELECT tData.GDP FROM tData WHERE tData.Country = 'Nigeria') AND (SELECT tData.GDP FROM tData WHERE tData.Country = 'Kenya'); Now all of you gurus probably know which of these limits is the lower one and which is the upper, but I don't! If I wanted, say, GDP values that were... lower bound <= Value < upper bound, I would have to go through more SQL contortions to get the desired result. What I'd like to see is an optional phrase that could be attached to the current BETWEEN...AND operator to make this sort of thing easier, say something like... BETWEEN ... AND ... IncExc (for including the lower limit but excluding the upper limit) or... BETWEEN ... AND ... ExcExc (for excluding the both limits) or... BETWEEN ... AND (which defaults to the currently used IncInc) Any thoughts? (or am I all wet?) -- Thanks, cinnie
From: KARL DEWEY on 29 Jan 2010 13:49 Using the between it does not matter which is the higher or lower. It works the same. For your EXCLUSION use NOT Between. -- Build a little, test a little. "cinnie" wrote: > hello gurus > > This isn't a question as much as it is a beginner's comment on > BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are > inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the > conditions... 10 <= Value <= 20). But there are times, especially when > working with intervals, when I might want non-overlapping values like, > say... 10 < Value <= 20, then 20 < Value <= 30, etc. This is easy to do > using... WHERE Value > 10 And Value <= 20 instead of BETWEEN...AND, but it > gets harder when we don't know which of the limits is the lower bound and > which is the upper bound. For example... > > SELECT tData.Country > FROM tData > WHERE tData.GDP BETWEEN (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Nigeria') > AND (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Kenya'); > > Now all of you gurus probably know which of these limits is the lower one > and which is the upper, but I don't! If I wanted, say, GDP values that > were... lower bound <= Value < upper bound, I would have to go through > more SQL contortions to get the desired result. > > What I'd like to see is an optional phrase that could be attached to the > current BETWEEN...AND operator to make this sort of thing easier, say > something like... > > BETWEEN ... AND ... IncExc > (for including the lower limit but excluding the upper limit) or... > > BETWEEN ... AND ... ExcExc > (for excluding the both limits) or... > > BETWEEN ... AND > (which defaults to the currently used IncInc) > > > Any thoughts? (or am I all wet?) > > -- > Thanks, cinnie
From: Bob Barrows on 29 Jan 2010 13:55 cinnie wrote: > hello gurus > > This isn't a question as much as it is a beginner's comment on > BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results > that are inclusive at both ends. (BETWEEN 10 and 20 gives values > meeting the conditions... 10 <= Value <= 20). But there are > times, especially when working with intervals, when I might want > non-overlapping values like, say... 10 < Value <= 20, then 20 < > Value <= 30, etc. This is easy to do using... WHERE Value > 10 > And Value <= 20 instead of BETWEEN...AND It can still be done with BETWEEN. For example, if your data was Integer: BETWEEN 11 AND 20 > , but it gets harder when we > don't know which of the limits is the lower bound and which is the > upper bound. For example... > And that's only because Access does not force you to put the operands in the correct order the way other sql dialects do. > BETWEEN ... AND ... IncExc > (for including the lower limit but excluding the upper limit) > or... > > BETWEEN ... AND ... ExcExc > (for excluding the both limits) or... > > BETWEEN ... AND > (which defaults to the currently used IncInc) > > > Any thoughts? (or am I all wet?) > I guess it would be nice to have, but we don't have the power to make it happen. It's been quite a while since JetSQL was updated, but, you could always try submitting this request to MS. I suspect they would reject the request as it's not per SQL standards. As to your problem, I would use VBA to solve it. Assign to the GDP from both countries to variables, then assign the lower of the two to a variable called lowlimit and the higher to a variable called highlimit, then pass those as parameters to your sql statement, increasing the value of the low limit if you want it excluded or vice versa with the high limit. -- HTH, Bob Barrows
From: Sylvain Lafontaine on 29 Jan 2010 16:47 When you want to use (> and <) or (>= and <=) but you don't know which one is lower or greater, the two possible SQL contortions that I see would be first to simply to use *both* cases: WHERE (tData.GDP > (SELECT tData.GDP FROM tData WHERE tData.Country = 'Nigeria') AND tData.GDP < (SELECT tData.GDP FROM tData WHERE tData.Country = 'Kenya') ) OR (tData.GDP > (SELECT tData.GDP FROM tData WHERE tData.Country = 'Kenya') AND tData.GDP < (SELECT tData.GDP FROM tData WHERE tData.Country = 'Nigeria') ) The 'wrong' case will return no result; so you get what you want. However, a better solution would be to use the Min() and Max() functions to retrieve the required result for both subqueries (untested): WHERE tData.GDP > (SELECT Min (tData.GDP) FROM tData WHERE tData.Country in ('Nigeria', 'Kenya')) AND tData.GDP < (SELECT Max (tData.GDP) FROM tData WHERE tData.Country in ('Nigeria', 'Kenya')) This way, you see that you don't really need to use an optional phrase or keyword. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "cinnie" <cinnie(a)discussions.microsoft.com> wrote in message news:10080434-4C38-4013-A7C4-BF1E272BA42A(a)microsoft.com... > hello gurus > > This isn't a question as much as it is a beginner's comment on > BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that > are > inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the > conditions... 10 <= Value <= 20). But there are times, especially > when > working with intervals, when I might want non-overlapping values like, > say... 10 < Value <= 20, then 20 < Value <= 30, etc. This is easy to > do > using... WHERE Value > 10 And Value <= 20 instead of BETWEEN...AND, but > it > gets harder when we don't know which of the limits is the lower bound and > which is the upper bound. For example... > > SELECT tData.Country > FROM tData > WHERE tData.GDP BETWEEN (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Nigeria') > AND (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Kenya'); > > Now all of you gurus probably know which of these limits is the lower one > and which is the upper, but I don't! If I wanted, say, GDP values that > were... lower bound <= Value < upper bound, I would have to go through > more SQL contortions to get the desired result. > > What I'd like to see is an optional phrase that could be attached to the > current BETWEEN...AND operator to make this sort of thing easier, say > something like... > > BETWEEN ... AND ... IncExc > (for including the lower limit but excluding the upper limit) or... > > BETWEEN ... AND ... ExcExc > (for excluding the both limits) or... > > BETWEEN ... AND > (which defaults to the currently used IncInc) > > > Any thoughts? (or am I all wet?) > > -- > Thanks, cinnie
From: KenSheridan via AccessMonster.com on 29 Jan 2010 19:24
Cinnie: I think you can probably also do it without subqueries: SELECT T1.Country, T1.GDP FROM TData As T1, TData As T2 WHERE T2.Country IN('Nigeria', 'Kenya') GROUP BY T1.Country, T1.GDP HAVING MAX(T2.GDP) > MIN(T1.GDP) AND MIN(T2.GDP) < MAX(T1.GDP); Ken Sheridan Stafford, England cinnie wrote: >hello gurus > >This isn't a question as much as it is a beginner's comment on >BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are >inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the >conditions... 10 <= Value <= 20). But there are times, especially when >working with intervals, when I might want non-overlapping values like, >say... 10 < Value <= 20, then 20 < Value <= 30, etc. This is easy to do >using... WHERE Value > 10 And Value <= 20 instead of BETWEEN...AND, but it >gets harder when we don't know which of the limits is the lower bound and >which is the upper bound. For example... > >SELECT tData.Country >FROM tData >WHERE tData.GDP BETWEEN (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Nigeria') > AND (SELECT tData.GDP > FROM tData > WHERE tData.Country = 'Kenya'); > >Now all of you gurus probably know which of these limits is the lower one >and which is the upper, but I don't! If I wanted, say, GDP values that >were... lower bound <= Value < upper bound, I would have to go through >more SQL contortions to get the desired result. > >What I'd like to see is an optional phrase that could be attached to the >current BETWEEN...AND operator to make this sort of thing easier, say >something like... > > BETWEEN ... AND ... IncExc > (for including the lower limit but excluding the upper limit) or... > > BETWEEN ... AND ... ExcExc > (for excluding the both limits) or... > > BETWEEN ... AND > (which defaults to the currently used IncInc) > >Any thoughts? (or am I all wet?) > -- Message posted via http://www.accessmonster.com |