# SQL Question
**URL:** https://boards.straightdope.com/t/sql-question/557994
**Category:** Factual Questions
**Created:** [October 22, 2010, 12:42pm UTC](https://boards.straightdope.com/t/sql-question/557994 "2010-10-22T12:42:23Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Eutychus](https://boards.straightdope.com/u/Eutychus)
#### Post date: [October 22, 2010, 12:42pm UTC](https://boards.straightdope.com/t/sql-question/557994/1 "2010-10-22T12:42:23Z")
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?)
---
### Author:  [@cmkeller](https://boards.straightdope.com/u/cmkeller)
#### Post date: [October 22, 2010, 1:14pm UTC](https://boards.straightdope.com/t/sql-question/557994/2 "2010-10-22T13:14:57Z")
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 \> ?))
---
### Author:  [@CandidGamera](https://boards.straightdope.com/u/CandidGamera)
#### Post date: [October 22, 2010, 1:19pm UTC](https://boards.straightdope.com/t/sql-question/557994/3 "2010-10-22T13:19:34Z")
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.
---
### Author:  [@Eutychus](https://boards.straightdope.com/u/Eutychus)
#### Post date: [October 22, 2010, 1:35pm UTC](https://boards.straightdope.com/t/sql-question/557994/4 "2010-10-22T13:35:18Z")
Sunuvabitch! It works!
I don’t know why it works. I’m pretty much an SQL newbie, but it does work, so thanks!
---
### Author:  [@CandidGamera](https://boards.straightdope.com/u/CandidGamera)
#### Post date: [October 22, 2010, 1:39pm UTC](https://boards.straightdope.com/t/sql-question/557994/5 "2010-10-22T13:39:34Z")
> [@Eutychus](#):
>
> 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!
---
### Author:  [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)
#### Post date: [October 22, 2010, 2:00pm UTC](https://boards.straightdope.com/t/sql-question/557994/6 "2010-10-22T14:00:37Z")
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.
---
### Author:  [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)
#### Post date: [October 22, 2010, 4:43pm UTC](https://boards.straightdope.com/t/sql-question/557994/7 "2010-10-22T16:43:54Z")
> [@Shagnasty](#):
>
> 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.
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.