find upper and lower value

[BubikolRamios]

data, ordered:

1
8
13
24
25
67

is it posible to retrieve this, given 13 as condition ?

8
13
24

[BubikolRamios]

found this link, has all answers there:http://www.artfulsoftware.com/infotree/queries.php?&bw=1259

[Old Pedant]

Okay, before I go look at that artfulsoftware stuff, let me take a whack at it:


SELECT * FROM table WHERE field < 13 ORDER BY field DESC LIMIT 1
UNION
SELECT * FROM table WHERE field >= 13 ORDER BY field ASC LIMIT 2

Now I'll go peek.

[Old Pedant]

I give up. Where did you find the answer in that "artful" stuff???? I sure didn't see it.

[BubikolRamios]

ok yours does look simple and interesting, but does not work --> incorerect usage of uniou and order by .....

as for your question: http://www.artfulsoftware.com/infotree/queries.php?&bw=1259#75
do need two queries one for prev and one for next. I like yours more, if it wold work ....

[BubikolRamios]

this gets yours working/geting right result

select * from
(
(SELECT * FROM galery_1 WHERE id_galery < 12 ORDER BY id_galery DESC LIMIT 1) A
)
UNION
SELECT * FROM galery_1 WHERE id_galery >= 12 ORDER BY id_galery ASC limit 3

[Old Pedant]

Oh, yeah...forgot. MySQL wants you to put parens around the SELECTs if you will use ORDER BY with them:

( SELECT * FROM table WHERE field < 13 ORDER BY field DESC LIMIT 1 )
UNION
( SELECT * FROM table WHERE field >= 13 ORDER BY field ASC LIMIT 2 )

Just tested it. That works.

Sure, you could also do a three way UNION, but then it has to be part of a sub-select:

SELECT * FROM table WHERE field IN (
SELECT max(field) FROM table WHERE field < 13
UNION
SELECT field FROM table WHERE field = 13
UNION
SELECT min(field) FROM table WHERE field > 13 )
ORDER BY field;

[BubikolRamios]

hmm I dont know why your first version was not working when I was trying, now it works (-:

anyway it would be interesting if it could be done in a way as at given link both prev and next in one query, so result would be in shape of row, like:


prev middle next
8 13 24

[BubikolRamios]

since already using subquerys , this would do it:


Select
( SELECT * FROM table WHERE field < 13 ORDER BY field DESC LIMIT 1 ) as prev
13,
( SELECT * FROM table WHERE field >13 ORDER BY field ASC LIMIT 1 ) as next
from table

[BubikolRamios]

this is complete for all records, gives you upper and lower neighbour


select P.Prev,N.id_galery as center, N.NEXT from
(SELECT a.id_galery, MIN(b.id_galery) AS Next
FROM galery_1 AS a
JOIN galery_1 AS b ON b.id_galery > a.id_galery
GROUP BY a.id_galery) as N
JOIN
(SELECT a.id_galery, MIN(b.id_galery) AS Prev
FROM galery_1 AS a
right JOIN galery_1 AS b ON a.id_galery > b.id_galery
GROUP BY b.id_galery) as P
on N.id_galery = P.id_galery


like:



8 13 24
13 24 56
24 56 58


note: this one hangs on not so big amount of data, even if id is indexed.