Re: sql select question

ChrisH wrote:
hilz wrote:
Hi all
this is more of an SQL question than a Java question.
I have a table with a SPEED column and some other columns like this:

SPEED SOME_OTHER_COLUMNS....
----- ------------------
100 some values here...
34
97
68
13
27
44


now I want to select the two rows that surround a value I am interested
in. For example, if I am interested in a value of 55, I want to select
the two rows that have a speed of 44 and 68.
Is there a way of doing that in SQL ?
i can handle the case where i get an exact match separately.
I just need to know how to select exactly two rows (the one above the
given value and the one below it).


Thanks for any help.

select *
from aTable
where speed = (select max(speed) from aTable where speed < 55)
or speed = (select min(speed) from aTable where speed > 55)


Ok... my situation got a little more complecated now. I hope it is still doable with one sql select.

here is what i have now...
the table i will refer to now is at the end of this message.
Now there are two columns i am interested in. speed and distance.
i have two values that i need to look up.
say i need a speed of 35 and a distance of 51. i want to select the rows where the speed is the one above or the one below 35 and the distance is just the one or the one below 51
so my query should return:

speed distance
----- --------
30 45
30 65
40 46
40 89


here is what i tried and it did not work obviously:

SELECT * FROM aTable WHERE (
(
speed=( select max(speed) from aTable where speed<35) or
speed=( select min(speed) from aTable where speed>35)
)
and
(
distance =( select max(distance) from aTable where distance<51 ) or
distance =( select min(distance) from aTable where distance>51 )
)
);

Any help is greatly apprecaite.

here is part of the table i am referring to:

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
....
.

Relevant Pages

  • Re: zip code search
    ... What you have to do is find a function that you can use in your sql ... query to calculate distance -- if you google the sqlserver.programming ... Then your query looks something like this: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: sql select question
    ... this is more of an SQL question than a Java question. ... I have a table with a SPEED column and some other columns like this: ... Is there a way of doing that in SQL? ...
    (comp.lang.java.databases)