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. Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/ 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 Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-896620 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) Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-896622 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. Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-896626 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 Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-896632 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 Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-896650 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. Link to comment https://forums.phpfreaks.com/topic/169965-sql-question/#findComment-903180 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.