Windowed Postgres Query

Rails
1

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?

2

On Aug 18, 10:44 pm, Mindtonic [email protected] wrote:

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?

In your example given a card like 2 you know it is the smallest one,
similarly you know that A is the greatest, that K is greatest bar one
and that 3 comes before 4. Do any of these sort of things hold for
your actual dataset?

Fred

3

Mindtonic wrote:

Hello. I have a database query I am trying to develop.

OK…but note that your question has absolutely nothing to do with
Rails, and would be better asked in an SQL forum.

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]

This part is easy: just use BETWEEN.

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]

This part is slightly less easy, but you can still do it in
(vendor-independent) SQL with clever use of min() and max(). And you
do want to do it in SQL: doing it in Ruby will result in unnecessary
extra queries.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]