mcmuney Posted August 12, 2009 Share Posted August 12, 2009 I have a situation where I need to pull 5 results from the DB. One of the results I will specify within the query; however, the remainder 4 needs to be two id's below and two above. For example: Data_ID 11 12 13 14 15 16 My query will be... WHERE Data_ID=13 Based on this query, I want the results to show: 11 12 13 (specified in query) 14 15 Currently, I'm getting the result, but utilizing 5 queries and I wanted to know if there's an easier way to do this. Thanks. Quote Link to comment Share on other sites More sharing options...
J.Daniels Posted August 12, 2009 Share Posted August 12, 2009 Just calculate the range before creating your SQL query then use BETWEEN Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted August 12, 2009 Share Posted August 12, 2009 WHERE Data_ID >= (13-2) AND `id` <= (13+2) Quote Link to comment Share on other sites More sharing options...
mcmuney Posted August 12, 2009 Author Share Posted August 12, 2009 But the issue is that the id's will not be in sequence. I just gave a simple example, but I can have a situation like this: Data_ID 1234 20000 30005 70000 250000 390000 In this case, if my query specified 7000, I'd need to see the following: 20000 30005 70000 (specified in query) 250000 390000 Thanks. Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted August 12, 2009 Share Posted August 12, 2009 Maybe (SELECT * FROM table WHERE id >= 70000 ORDER BY id ASC LIMIT 3) UNION (SELECT * FROM table WHERE id < 7000 ORDER BY id DESC LIMIT 2) However it probably isn't very efficient Quote Link to comment Share on other sites More sharing options...
mcmuney Posted August 12, 2009 Author Share Posted August 12, 2009 I reversed the UNION, which gets me close, having some issues with the order. I got it to show like this, but notice how the first two are in reversed order. If I change the DESC to ASC, then it pulls numbers from the very beginning. 30005 20000 70000 (specified in query) 250000 390000 Maybe (SELECT * FROM table WHERE id >= 70000 ORDER BY id ASC LIMIT 3) UNION (SELECT * FROM table WHERE id < 7000 ORDER BY id DESC LIMIT 2) However it probably isn't very efficient Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2009 Share Posted August 21, 2009 Actually, that likely is the most efficient option, particuarly in earlier version of mysql. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.