asked on

# query with a twist

Hi Guys,

i have a strange request for a query.  lets say i have a table that looks like the following:

x
0
8
10
15

i want a query that will return the closest two y values.

so for example if my input is 9 i should get a query that returns two values in one column. (8,10).

I wrote something that gets very close:

select x  from (
select x, abs(x -9) from test order by 2
) where rownum < 3 order by x

this works when i give it 17 i get back 10 and 15

but it doesnt work when i give it 6 :(  it gives me 8 and 10 when i actually want 0 and 8. This is because 10 is closer to 6 that 0.  So i need a new approach.

Thanks
Sean Stuber

what is the criteria for deciding to take the 2 closest values and when NOT to?

if 9 -> 8,10 is correct
and 17 ->  10, 15 is correct

why is 6 -> 8,10  wrong?

or why would  6 -> 0,8  be correct?

as you said   10 is closer to 6 than 0, so 10 should be correct by your rule, so there must be some other rule that overrides "closest"

ASKER

closest being in order.

so

rownum x
1            0
2            8
3            10
4            15

ideally i want a value either side of the one i provide unless i am out of bounds and then i take the closes two. So if i say 6 i want 0 and 8 because they are either side of 6.  if i say 17 there is no value greater than 17 so i want 10 and 15.  hope thats clear
-- maybe???

DECLARE @parm int
SET @parm = 17

SELECT TOP 2 a.*
FROM
(SELECT
TOP 2 [x] FROM [test] WHERE [x] < @parm ORDER BY [x] DESC
UNION
(SELECT TOP 1 [x] FROM [test] WHERE [x] > @parm ORDER BY [x])
) a
ORDER BY a.[x] DESC
what do you expect if your value is in the table?

0,8,10,15   - what are the expected results for each of those?
SOLUTION
Sean Stuber

membership
Create an account to see this answer
Signing up is free. No credit card required.
ASKER CERTIFIED SOLUTION

membership
Create an account to see this answer
Signing up is free. No credit card required.

ASKER

I've requested that this question be deleted for the following reason:

I didnt get a reasonable answer.... maybe it wasnt suitable for a query.
please explain what wasn't adequate in the answers above.

at least provide a counter example where the answers above don't produce the requested results (note, there was some ambiguity in the request)
What did you see as not being reasonable about my suggestion?

ASKER

hi awking00

sorry for the delay.

One problem with your query is that it returns the values in two columns. I know i can resolve this with a union but that makes it even longer

in my request i asked for one column with two values:

so for example if my input is 9 i should get a query that returns two values in one column. (8,10).

However the bigger issue is that i was looking for something that was neat and didnt produce a complex query that was difficult to read.

After getting suggestions i decided to change the way the code works and do it procedurally by searching for the max value less that the given value and the min value greater than the given value.  This produced cleaner code.

thanks all the same and your query certainly is clever.

Asim

ASKER

sdstuber that is a very impressive query!  well done!

I think this is a pretty good answer but again after getting suggestions i decided to change the way the code works and do it procedurally by searching for the max value less that the given value and the min value greater than the given value.  This produced cleaner code.

i will split the points between you two as you both provided workable solutions.

Regards,
why the B?

Penalty grades should only be given when there is a demonstrable failing in the answers.
Either wrong results or insufficient detail to allow you to proceed.

ASKER

B?  penalty grade?  im not sure what that is or how to modify it.

I thought that the problem was fairly simple and I was hoping for a relativly simple query but it looks like that isn't possible.

The answers themselves are very clever and im sure will be useful for future queries.

Thanks
When you closed the question you assigned a B grade.

Anything other than an A is a penalty and the deficiencies should be explained.

By your descriptions it appears there were no deficiencies in the suggested answers, you simply chose to pursue a different route.

"relatively simple" - is itself "relative"  :)

for some, both of the answers above could look like arcane magic, for others they are fairly straight forward.

Since a Moderator is already monitoring this thread, if you wish to correct your grade, simply say so and I'm sure modus_operandi will be happy to reopen the question for you. If you do not, please explain why the B grades are justified.

ASKER

im happy to change to an A if the moderator can do this.  I didnt realise B was a penalty but only selected it because it didnt solve the problem that i hadnt fully explained.

Thanks
I missed the part about one column. Just modify my query as follows:
with cte as
(select
lag(x) over (order by x) prevx,
x,
lead(x) over (order by x) nextx,
&num num
from tablex)
select max(first_num)||','||max(second_num) two_closest from
(select num, prevx, x, nextx,
case when num >= prevx and num < x then prevx
when prevx is null and num < x then x
when nextx is null and num >= x then prevx
end as first_num,
case when num >= prevx and num < x then x
when prevx is null and num < x then nextx
when nextx is null and num >= x then x
end as second_num
from cte)
;