sql select question

Thread view:

hilz - 25 Apr 2006 18:11 GMT

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.

Reply to this Message

ChrisH - 25 Apr 2006 19:31 GMT

> Hi all
> this is more of an SQL question than a Java question.
[quoted text clipped - 19 lines]
>
> 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)

Reply to this Message

hilz - 25 Apr 2006 20:32 GMT

>> Hi all
>> this is more of an SQL question than a Java question.
[quoted text clipped - 24 lines]
> where speed = (select max(speed) from aTable where speed < 55)
> or speed = (select min(speed) from aTable where speed > 55)

Chris
That is brilliant.
I've been using SQL for few years now, but I never needed it for more
than simple select cases.
I am yet to discover the real power of SQL!

Thank you for your answer.

Reply to this Message

hilz - 27 Apr 2006 18:08 GMT

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

Reply to this Message

ChrisH - 28 Apr 2006 15:45 GMT

...

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

....

Hi, I almost gave up on this one 9^) but finally have a solution.
However, its ugly (IMHO) and I cannot guarantee it works in all cases.
Here it is:

SELECT *
FROM (SELECT *
FROM chtmp
WHERE (( speed = (SELECT MAX (speed)
FROM chtmp
WHERE speed < 35)
OR speed = (SELECT MIN (speed)
FROM chtmp
WHERE speed > 35)
)
)) spd
WHERE ( spd.distance = (SELECT MAX (distance)
FROM chtmp
WHERE distance < 51
AND speed = spd.speed)
OR spd.distance = (SELECT MIN (distance)
FROM chtmp
WHERE distance > 51
AND speed = spd.speed)
);

It uses the first sub-query to get the records with the right speed,
then the where clause picks out the desired distances for each speed.

cheers
Chris

Reply to this Message

sql select question

Free Magazines