| View previous topic :: View next topic |
| Author |
Message |
hilz
Guest
|
 Posted: Tue Apr 25, 2006 5:12 pm Post subject: sql select question |
 |
|
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. |
|
| Back to top |
|
 |
ChrisH
Guest
|
| Posted: Tue Apr 25, 2006 7:12 pm Post subject: Re: sql select question |
|
|
hilz wrote:
| Quote: | 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) |
|
| Back to top |
|
|
hilz
Guest
|
| Posted: Tue Apr 25, 2006 8:12 pm Post subject: Re: sql select question |
|
|
ChrisH wrote:
| Quote: | 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)
|
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. |
|
| Back to top |
|
|
hilz
Guest
|
| Posted: Thu Apr 27, 2006 5:12 pm Post subject: Re: sql select question |
|
|
ChrisH wrote:
| Quote: | 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
.... |
|
| Back to top |
|
|
ChrisH
Guest
|
| Posted: Fri Apr 28, 2006 3:12 pm Post subject: Re: sql select question |
|
|
hilz wrote:
....
| Quote: | 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 |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
