Hello. I have a database query I am trying to develop.
For the purpose of explanation, I am going to use a suit of playing
cards in ascending order as an example. The complete data set would
look like this: [2, 3, 4, 5, 6, 7, 8, 9, 10, J, Q, K, A]
The WHERE clause would contain "suit = hearts" and the ORDER would be
"value ASC". I need to be able to page through the results, so there
should be a LIMIT. So a basic example would be "SELECT * FROM cards
WHERE suit = hearts ORDER value LIMIT 5" and would yield [2, 3, 4, 5,
6]
I need a database query that is "windowed" around a card. The query
would return a set of data almost identical in application to windowed
pagination links.
If the targeted number is in the middle of the set, the query should
return the number with 2 records on either side of it:
Requesting 5 => [3, 4, 5, 6, 7]
Requesting 7 => [5, 6, 7, 8, 9]
If it is near the beginning or the ending, it should return the number
and as many additional records on either side so that the total number
of records is 5.
Requesting 2 => [2, 3, 4, 5, 6]
Requesting K => [10, J, Q, K, A]
If this query is run against a set with less than 5 records, it
returns all 5 records.
What function, approach or theory should I utilize to accomplish
this? What would the query look like?
