Pulling back 3 results (not as easy as adding limit)

Discussion in 'Databases' started by imvain2, Jul 25, 2008.

  1. #1
    Sorry, this isn't as easy as just placing a limit 3 at the end.

    Assuming the first column is article_id and the second is article_title. And Article_ID may not always be consecutive.

    1 A
    2 B
    3 C
    4 D
    5 E
    6 F
    7 G
    8 H
    9 I
    10 J


    So if I pass ID = 4, I would expect 3,4,5 brought back.
    Or if I pass ID = 7, I would expect 6,7,8 brought back.

    *Please remember, the ID #s are for samples and the ID # won't be consecutive.

    I have the below SQL
    select article, article_title from articles
    where (article_id = $article_id
    or article_id > $article_id
    or article_id < $article_id)
    order by article_title limit 3;
    Code (markup):
    but it doesn't work.

    Essentially, I want to pull one article, the previous article and the next article.

    Thank You.
     
    imvain2, Jul 25, 2008 IP
  2. klimu

    klimu Peon

    Messages:
    21
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I suppose you could use UNION and combine three clauses..

    (SELECT article, article_title FROM articles
    WHERE article_id < $article_id ORDER BY ID DESC LIMIT 1) union
    (SELECT article, article_title FROM articles
    WHERE article_id = $article_id) union
    (SELECT article, article_title FROM articles
    WHERE article_id > $article_id ORDER BY ID ASC LIMIT 1);

    or two..

    (SELECT article, article_title FROM articles
    WHERE article_id < $article_id ORDER BY ID DESC LIMIT 1) union
    (SELECT article, article_title FROM articles
    WHERE article_id => $article_id ORDER BY ID ASC LIMIT 2);
     
    klimu, Jul 27, 2008 IP
  3. imvain2

    imvain2 Peon

    Messages:
    218
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    klimu. Thats what I was looking for

    Thank You.
     
    imvain2, Jul 27, 2008 IP