select before and after question

[Eli Nurman]

Hi, I Wonder if it possible to retrieve from an SQL server a recored plus its 5 recored before anf after. for example: When I search a product with serial-number "100" i would like to retrieve 11 recorods from serial-number "095" until "105", of course after a "ORDER BY" statement of the serial-number field

[Luc Pattyn]

???

WHERE ID BETWEEN 95 AND 105

depending on the database the limit values will or will not be included (then change to 94 AND 106) :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[Chris Meech]

where serial-number between (100-5) and (100+5)
order by seriual-number

Does that do the trick? :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Eli Nurman]

Thanks. But this could be a good solution when querying a numeric field , my main problem is when querying a varchar field like product descriptions

[Jorgen Andersson]

Assuming that your description field is unique:

SELECT table.*
FROM table,(
SELECT id
FROM table
WHERE description = 'whatever'
) temp
WHERE table.id BETWEEN (temp.id - 5) AND (temp.id + 5)

"When did ignorance become a point of view" - Dilbert

[Eli Nurman]

thanks. this is an excelent solutions but when a few descriptions are 'whatever' is will appear a few times....

[Not Active]

Oh come on! Do you want someone to right it for you? You have more than enough to figure out the rest on your own.

---

I know the language. I've read a book. - _Madmatt

[Eli Nurman]

ok, i am just climbing down the tree...

[Mycroft Holmes]

Wheres that spoon icon?

Never underestimate the power of human stupidity RAH