SELECT in mysql

Question

Possible Duplicate:
selecting items that come after a specific value

i asked this allready bu explained it very bad so i'm gonna do it right this time.

I have a mysql table like this:

    ISBN            title                   author          price   amount

    978-0060850524  A Brave New World   Aldous Huxley   6.49    7   
    978-0061743528  To Kill a Mockingbird   Harper Lee  12.89   0   
    978-0142000670  Of Mice and Men     John Steinbeck  5.99    89  
    978-0452284234  Nineteen Eighty-Four    George Orwell   10.85   90  
    978-0452284241  Animal Farm             George Orwell   9.80    24  
    978-0553574166  The Hutt Gambit     A.C. Crispin    7.99    2   
    978-0553578850  The Mandalorian Armor.  K.W. Jeter  7.99    20  
    978-0571056866  Lord of the Flies   William     7.70    45 
978-0765340788  The Machine Crusade     Brian Herbert   7.99    14  
978-0861404216  The Colour of Magic     Terry       29.16   3

I want to create a query wich does the following. I have a variable wich contains the ISBN of 1 of the products. the query should select all the products ordered by the ISBN bust starting with the ISBN that was given in the variable. so if the ISBN is from item 4 the result should show all 10 books starting with item 5(yes it should start from 1 item after the item in the variable then 6,7,8,9,10,1,2,3,4 ,5

Answer 1

Only solution I see would be two queries with UNION :

SELECT *
FROM yourtable
WHERE ISBN = 'Your ISBN'
UNION
SELECT *
FROM (
    SELECT *
    FROM yourtable
    WHERE ISBN <> 'Your ISBN'
    ORDER BY ISBN
    LIMIT 9
    );

I'm not a huge fan of ORDER BY and LIMIT in subqueries, though...

Answer 2

SELECT  *
FROM    (
        SELECT  *
        FROM    books bo
        WHERE   isbn >
                (
                SELECT  isbn
                FROM    books bi
                WHERE   id = 4
                )
        ORDER BY
                isbn
        LIMIT 10
        ) q
UNION ALL
SELECT  *
FROM    (
        SELECT  *
        FROM    books bo
        ORDER BY
                isbn
        LIMIT 10
        ) q
LIMIT 10

Answer 3

Something like this should perform well:

(SELECT * FROM t1 WHERE ISBN > '978-0452284234' ORDER BY ISBN)
UNION ALL
(SELECT * FROM t1 WHERE ISBN <= '978-0452284234' ORDER BY ISBN)

Answer 4

You can do this in a single query in MySql, like so:

SELECT * FROM `yourtable`
order by ISBN>'Your ISBN' desc, ISBN asc

Note: using ISBN>'Your ISBN' desc in the order by will ensure that the higher ISBNs appear first, followed by the lower ones. Thereafter each group is ordered by ISBN.