I’m writing an SQL query which is going to be returning one record based on a QueryStringParameter.
Is there a way to return, as part of this set, the records directly before and directly after the one record returned as well? (So three record would be returned?)
I’d suggest the following, where the question mark is your parameter:
select * from table where (key_field = ?) or (key_field = (select max(key_field) from table where key_field < ?)) or (key_field = (select min(key_field) from table where key_field > ?))
What cmkeller said, assuming you mean “before” and “after” based on an ordering of the data on the QueryStringParameter. It gets more complicated to do(a little) if the data is ordered on some other field.
Sunuvabitch! It works!
I don’t know why it works. I’m pretty much an SQL newbie, but it does work, so thanks!
The magic of subqueries!
The solution given is a good one and I am glad that it works for you but there is a theoretical problem with your question. Data stored in relational databases is inherently unordered in the database so there are no ‘before’ and ‘after’ records for a given record, only results of the query itself. That might not mean much for your problem here but it could if you keep trying to do things like this. The solution here could return more than three records for example if your key field isn’t unique and it will only produce two records if you search for the record with the smallest or largest value in the key field because there isn’t anything before and after those respectively. A well designed key helps deal with this situation but the key may not order the records the same way you want to in the query.
You can force SQL to do things like this obviously but you can run into complications and you have to define exactly what you mean by ‘before’ and ‘after’ each time and build your subqueries to handle all scenarios which can get complicated.
I’ll take a moment to expand on this a little bit:
In a database, there is physical order, which describes the order of the data on the physical media where it is stored, and logical order, which describes the order of the data as it is perceived by the database engine.
Unless you use a clustered index, your physical order is never guaranteed to be consistent, or even repeatable. A query one moment that returns rows in order 1234 might return them next as 1342. Using a clustered index in effect tells the db engine to store the records in a physical order.
Your logical order is determined by the query you write, as described above by Shagnasty. It can be adjusted using statements like “order by”, “top” and verious other keywords. Shagnasty did point out a couple of instances where you might come up with fewer than expected results, and you’d be wise to understand why this happens and make sure it fits with how you plan to use the data.