SQL Question

[mcmuney]

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.

 

 

[J.Daniels]

Just calculate the range before creating your SQL query then use BETWEEN

[wildteen88]

WHERE Data_ID >= (13-2) AND `id` <= (13+2)

[mcmuney]

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.

[wildteen88]

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

[mcmuney]

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

[fenway]

Actually, that likely is the most efficient option, particuarly in earlier version of mysql.