March 29, 2012 at 4:28 pm
hi, i want know how to get the ages between 25-28 (from ages 18 until 45)
when i do age between(25,28) its displays me data but
when i do age between (28,25) its not display data.
is this normal?
March 29, 2012 at 4:30 pm
yaniv.zisano (3/29/2012)
hi, i want know how to get the ages between 25-28 (from ages 18 until 45)when i do age between(25,28) its displays me data but
when i do age between (28,25) its not display data.
is this normal?
In SQL, yes. The first value must be less than or equal to the second.
March 29, 2012 at 4:50 pm
thanks for replay.
Is there any way to do it in a way that it display
me the data even if
The first value is less than or equal to the second
.
I mean that it will show me the data of ages between (25,28) and (28,25)
March 29, 2012 at 5:04 pm
can i do this?
where age=<28 and age=<25
March 29, 2012 at 5:53 pm
A quick and simple answer is No you can not
CREATE TABLE #A(Age INT,Id INT IDENTITY(1,1))
DECLARE @X INT
SET @X = 20
WHILE @X < 30
BEGIN
INSERT INTO #A
SELECT @X
SET @X = @X + 1
END
SELECT Age,ID FROM #A
SELECT Age,ID FROM #A where age<=28 and age <=25
--note your code =<28 and age=<25
-- DROP TABLE #A
Result of 2nd SELECT
Age ID
201
212
223
234
245
256
--Note that this does NOT give you what I think you want !!!
March 29, 2012 at 6:00 pm
yaniv.zisano (3/29/2012)
thanks for replay.Is there any way to do it in a way that it display
me the data even if
The first value is less than or equal to the second
.
I mean that it will show me the data of ages between (25,28) and (28,25)
Why does it matter? Using BETWEEN, the first value must be less than or equal to the second.
March 29, 2012 at 8:01 pm
I know this is going to seem silly but I'm assuming 25 and 28 are not hardcoded but they come in as parameters that the OP has no control over so either could be the MIN.
In that case, you could do it like this:
DECLARE @a TABLE (age INT)
DECLARE @agemin INT, @agemax INT
INSERT INTO @a
SELECT 17 AS Age UNION ALL SELECT 19 UNION ALL SELECT 20
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26
SET @agemin = 28
SET @agemax = 25
SELECT age
FROM @a
WHERE (CASE WHEN @agemin < @agemax THEN age ELSE 99-age END) BETWEEN
(CASE WHEN @agemin < @agemax THEN @agemin ELSE 99-@agemin END) and
(CASE WHEN @agemin < @agemax THEN @agemax ELSE 99-@agemax END)
Of if you don't like the messy CASE statements in your select, simply do the arithmetic ahead of time:
IF @agemin > @agemax
SELECT @agemin = 99-@agemin, @agemax = 99-@agemax
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 30, 2012 at 2:56 am
SSCoach its matter because i want the
user to select age from from range of number (select box - min) to range of number (select box - max)
and also select range of number (select box - max) to range of number (select box - min)
for example i want all the ladies between ages 25 to 28
in other hand i want all the ladies between ages 28 to 25
its the same logic.
dwain.c you are right 25 and 28 are not hardcoded they are parameters
March 30, 2012 at 3:05 am
you would have to build in some logic to the parameters like
if @minage < @maxage do nothing
if @minage > @maxage set @minage = @maxage, @maxage = @minage --needs to be done at the same time, cannot set one, then set the other
so that the values are swaped round and then use the between so that the minimum value is alwaus on the left and the maximum value is always on the right
March 30, 2012 at 3:08 am
its the same logic.
Not in my opinion. In statements such as 'between a and b', it is generally assumed that 'a' is smaller, less or fewer than 'b', whatever a and b represent.
The proposed solution gets round that, for your special case.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2012 at 3:27 am
anthony.green
this is now my code like you said:
DECLARE @minage INT
DECLARE @maxage INT
SET @minage = @FromAge
SET @maxage = @ToAge
--if @minage < @maxage do nothing
if @minage > @maxage set @minage = @maxage
if @minage > @maxage set @maxage = @minage
...where dbo.fAgeCalc(u.BDate,GETDATE()) BETWEEN @minage and @maxage
and still not showing me the data, it the same as the between
April 2, 2012 at 7:35 am
-- Won't work
if @minage > @maxage set @minage = @maxage
if @minage > @maxage set @maxage = @minage
-- Will work
if @minage > @maxage select @minage = @maxage, @maxage = @minage
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 2, 2012 at 7:46 am
anthony.green (3/30/2012)
you would have to build in some logic to the parameters likeif @minage < @maxage do nothing
if @minage > @maxage set @minage = @maxage, @maxage = @minage --needs to be done at the same time, cannot set one, then set the other
so that the values are swaped round and then use the between so that the minimum value is alwaus on the left and the maximum value is always on the right
as per my post and to reitterate what dwain has just said, you need to do it at the same time, you cannot do one then do the other
Going through your code
DECLARE @minage INT
DECLARE @maxage INT
SET @minage = @FromAge -- sets it to 1000
SET @maxage = @ToAge -- sets it to 10
--if @minage < @maxage do nothing
if @minage > @maxage set @minage = @maxage sets minage to 10
if @minage > @maxage set @maxage = @minage sets maxage to 10
Doing it this way (HAS TO BE ON THE SAME LINE)
DECLARE @minage INT
DECLARE @maxage INT
SET @minage = @FromAge -- sets it to 1000
SET @maxage = @ToAge -- sets it to 10
--if @minage < @maxage do nothing
if @minage > @maxage set @minage = @maxage, @maxage = @minage --sets minage to 10, sets maxage to 1000
April 2, 2012 at 8:25 am
-- Also works
if @minage > @maxage set @minage = @maxage + @minage
if @minage > @maxage set @maxage = @minage - @maxage
if @minage > @maxage set @minage = @minage - @maxage 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 2, 2012 at 9:18 am
yaniv.zisano (3/30/2012)
SSCoach its matter because i want theuser to select age from from range of number (select box - min) to range of number (select box - max)
and also select range of number (select box - max) to range of number (select box - min)
for example i want all the ladies between ages 25 to 28
in other hand i want all the ladies between ages 28 to 25
its the same logic.
dwain.c you are right 25 and 28 are not hardcoded they are parameters
You are correct that mathematically between 25 and 28 == between 28 and 25 (== is read as equivalent).
However, SQL Server does not implement it that way. For the BETWEEN to work the first value must be less than or equal to the second value.
You have been shown how to ensure that the parameters @min-2 and @max-2 meet that requirement.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply