sql challenging question

[hilz]

Hi all..
I have this situation where i am unable to write the select statement
that will do what i want.

i have a table ATABLE with a number of columns, two of which are SPEED
and DISTANCE.

as follows:
SPEED DISTANCE
----- --------
10 74
10 64
10 63
20 57
20 64
20 53
20 97
30 <= 43
30 <= 45 <=
30 <= 65 <=
30 <= 87
40 <= 23
40 <= 46 <=
40 <= 86 <=
40 <= 94
50 35
50 46
50 76
50 97
....

I have two numbers that i want to work with. let's say i have :
SPEED = 35
DISTANCE = 50

Now what I want to do is that I want to select the rows where the SPEED
equals the first value greater and smaller than 35,(i.e. 20 and 40), and
at the same time, the distance is the first value greater and lower than
50

in other words, the select statement needs to return to me the following
rows:

30 <= 45 <=
30 <= 65 <=
40 <= 46 <=
40 <= 86 <=


any help is greatly appreciated.

 

[Tom Ellison]

Dear Hilz:

Sounds like fun! OK, so I'm perverse.

I would write 4 queries and make a UNION ALL of them. I'm going to code
them L for less than and G for greater than, so there are 4 solutions, LL,
LG, GL, and GG.

I created another table, Target, which contains the values 35/50 and as many
others as you wish.

Now, what if there were values in ATABLE of 34/43 and 33/49? Would the LL
solution be 34/49? Your question indicates it has to be a pair that exists
in ATABLE, right? So, is it 34/43 or is it 33/49? 34 is closer to 35, but
49 is closer to 50. So, which one is chosen? I will choose the one that
has the lesser cumulative difference. 34/43 scores 1 + 7 = 8 while 33/49
scores 2 + 1 = 3, which is the lower score. But there could be a tie score.
If we add 34/48 to the mix, that has a score of 3 as well. I'll break this
tie by using the difference of the SPEED values, so 34/48 wins.

Sorry to make so many assumptions, but I do not see where you have defined
the problem unambiguously. Ambiguity is our enemy here!

Without trying to implement all of this, here's an initial query solution:

SELECT T.Speed AS TargetSpeed, T.Distance AS TargetDistance, A.Speed,
A.Distance
FROM Target T, ATABLE A
WHERE T.Speed - A.Speed + T.Distance -A.Distance =
(SELECT MIN(T.Speed - A.Speed + T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)

This is meant to give the LL solution without implementing tie breaking
using SPEED, as I suggested. If there are ties, perhaps you'd rather see
all of them. This approach allows for that. So, a possible solution would
be to create 3 similar queries for the LG, GL, and GG solutions.

I'll stop here for now and see what it is you want.

Tom Ellison

 

[Tom Ellison]

Dear Hilz:

I've added some intermediate values, temporarily, and fixed a problem I
found:

SELECT T.Speed AS TargetSpeed, T.Distance AS TargetDistance, A.Speed,
A.Distance,
T.Speed - A.Speed AS DeltaSpeed, T.Distance - A.Distance AS
DeltaDistance,
(SELECT MIN(T.Speed - A.Speed + T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)
AS Diff
FROM Target T, ATABLE A
WHERE T.Speed - A.Speed + T.Distance - A.Distance =
(SELECT MIN(T.Speed - A.Speed + T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)
AND T.Speed >= A.Speed
AND T.Distance >= A.Distance

Without the intermediate values, it is:

SELECT T.Speed AS TargetSpeed, T.Distance AS TargetDistance, A.Speed,
A.Distance
FROM Target T, ATABLE A
WHERE T.Speed - A.Speed + T.Distance - A.Distance =
(SELECT MIN(T.Speed - A.Speed + T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)
AND T.Speed >= A.Speed
AND T.Distance >= A.Distance

Tom Ellison

 

[hilz]

Now, what if there were values in ATABLE of 34/43 and 33/49? Would the LL
solution be 34/49? Your question indicates it has to be a pair that exists
in ATABLE, right? So, is it 34/43 or is it 33/49? 34 is closer to 35, but
49 is closer to 50. So, which one is chosen? I will choose the one that
has the lesser cumulative difference. 34/43 scores 1 + 7 = 8 while 33/49
scores 2 + 1 = 3, which is the lower score. But there could be a tie score.
If we add 34/48 to the mix, that has a score of 3 as well. I'll break this
tie by using the difference of the SPEED values, so 34/48 wins.

Tom, Thanks for giving this a try with me!
The first critiria is the SPEED. so 34 is closer to 35, therefore, 34
will be picked first, and from there, the DISTANCE is picked next....
so the answer will be 34/43

i guess this will drastically simplify the queries you described...


oh and one more thing... I am hoping to be able to do it in ONE
statement. i want to be able to execute it once and get those 4 rows i
am interested in. i don't want to create a query and then perform a
select on this query....


thanks

 

[Tom Ellison]

Dear Hilz:

Making a modest change, I have given the SPEED a weight 1000 times as greate
as the DISTANCE. This will probably make it do what you want.

SELECT T.Speed AS TargetSpeed, T.Distance AS TargetDistance,
A.Speed, A.Distance
FROM Target AS T, ATABLE AS A
WHERE (T.Speed - A.Speed) * 1000 + T.Distance - A.Distance =
(SELECT MIN((T.Speed - A.Speed) * 1000 +
T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)
AND T.Speed >= A.Speed
AND T.Distance >= A.Distance;

Please look this over and test it. We can put together the other 3 queries
and wrap this up as soon as you approve this first one.

Tom Ellison

 

[Tom Ellison]

Dear Hilz:

So, jumping the gun a bit, here's a complete solutions attempt:

SELECT "GG" As Solution, T.Speed AS TargetSpeed, T.Distance AS
TargetDistance,
A.Speed, A.Distance
FROM Target AS T, ATABLE AS A
WHERE (A.Speed - T.Speed) * 1000 + A.Distance - T.Distance =
(SELECT MIN((A.Speed - T.Speed) * 1000 +
A.Distance - T.Distance)
FROM ATABLE A
WHERE A.Speed >= T.Speed
AND A.Distance >= T.Distance)
AND T.Speed <= A.Speed
AND T.Distance <= A.Distance
UNION ALL
SELECT "GL" AS Solution, T.Speed AS TargetSpeed, T.Distance AS
TargetDistance, A.Speed, A.Distance
FROM Target AS T, ATABLE AS A
WHERE (A.Speed - T.Speed) * 1000 + T.Distance - A.Distance =
(SELECT MIN((A.Speed - T.Speed) * 1000 +
T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed >= T.Speed
AND A.Distance <= T.Distance)
AND T.Speed <= A.Speed
AND T.Distance >= A.Distance
UNION ALL
SELECT "LG" AS Solution, T.Speed AS TargetSpeed, T.Distance AS
TargetDistance, A.Speed, A.Distance
FROM Target AS T, ATABLE AS A
WHERE (T.Speed - A.Speed) * 1000 + A.Distance - T.Distance =
(SELECT MIN((T.Speed - A.Speed) * 1000 +
A.Distance - T.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance >= T.Distance)
AND T.Speed >= A.Speed
AND T.Distance <= A.Distance
UNION ALL
SELECT "LL" AS Solution, T.Speed AS TargetSpeed, T.Distance AS
TargetDistance, A.Speed, A.Distance
FROM Target AS T, ATABLE AS A
WHERE (T.Speed - A.Speed) * 1000 + T.Distance - A.Distance =
(SELECT MIN((T.Speed - A.Speed) * 1000 +
T.Distance - A.Distance)
FROM ATABLE A
WHERE A.Speed <= T.Speed
AND A.Distance <= T.Distance)
AND T.Speed >= A.Speed
AND T.Distance >= A.Distance
ORDER BY TargetSpeed, TargetDistance, Solution DESC

Tom Ellison

 

[Van T. Dinh]

What do you want to do if some of your rows have SPEED of exactly 35? If you
decide to select this, does it count as SPEED-first-greater-than or
SPEED-first-less-than 35?

What do you want to do if, for example, the row with SPEED of 34 (assumed to
be first-less-than 35) has DISTANCE of exactly 50? If you decide to select
this, does it count as DISTANCE-first-greater-than or
DISTANCE-first-less-than 50?

 

[Tom Ellison]

Yes, Van, a very good question. I coded to allow a value that is equal to
be in both the greater or the less than categories, because if the SPEED
matches exactly, there could be a DISTANCE that is less and another that is
more. But what to do if both SPEED and DISTANCE match. Again, I coded an
exact match to be permitted both in the less than and in the greater than
category. But that could allow a single row to be all 4 solutions.

This may be worth your time to consider.

Tom Ellison

 

[hilz]

Tom,
Thanks for your help.
It seems this is too complicated to do using SQL.
Instead of spending the time to figure out a way to get it to work this
way, I took another rout and handled the situation in the code rather
than in SQL.

Thank you for your help anyway.
Regards.