# Windowed Postgres Query **URL:** \ **Category:** Rails\ **Created:** [August 18, 2010, 11:55pm UTC](https://www.ruby-forum.com/t/windowed-postgres-query/193509 "2010-08-18T23:55:40Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![mindtonic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f19dbf/32.png) [@mindtonic](https://www.ruby-forum.com/u/mindtonic)\ **Post date:** [August 18, 2010, 11:55pm UTC](https://www.ruby-forum.com/t/windowed-postgres-query/193509/1 "2010-08-18T23:55:40Z")
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? ---
**Author:** ![mindtonic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f19dbf/32.png) [@mindtonic](https://www.ruby-forum.com/u/mindtonic)\ **Post date:** [August 19, 2010, 12:31am UTC](https://www.ruby-forum.com/t/windowed-postgres-query/193509/2 "2010-08-19T00:31:23Z")
On Aug 18, 10:44 pm, Mindtonic [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![mindtonic](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/f19dbf/32.png) [@mindtonic](https://www.ruby-forum.com/u/mindtonic)\ **Post date:** [August 19, 2010, 12:32am UTC](https://www.ruby-forum.com/t/windowed-postgres-query/193509/3 "2010-08-19T00:32:54Z")
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](http://www.marnen.org) removed\_email\_address@domain.invalid