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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
