Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    find upper and lower value

    data, ordered:
    Code:
    1
    8
    13
    24
    25
    67
    is it posible to retrieve this, given 13 as condition ?
    Code:
    8
    13
    24

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    found this link, has all answers there:http://www.artfulsoftware.com/infotr...s.php?&bw=1259

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Okay, before I go look at that artfulsoftware stuff, let me take a whack at it:

    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    I give up. Where did you find the answer in that "artful" stuff???? I sure didn't see it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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/infotr...hp?&bw=1259#75
    do need two queries one for prev and one for next. I like yours more, if it wold work ....

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    this gets yours working/geting right result
    Code:
    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

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Oh, yeah...forgot. MySQL wants you to put parens around the SELECTs if you will use ORDER BY with them:
    Code:
    ( 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:
    Code:
    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;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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:

    Code:
    prev middle  next
    8       13     24

  • #9
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    since already using subquerys , this would do it:

    Code:
    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

  • #10
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    this is complete for all records, gives you upper and lower neighbour

    Code:
    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:

    Code:
    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.
    Last edited by BubikolRamios; 12-16-2009 at 09:11 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •