Windowed Postgres Query

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?

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

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,