From: | Mindtonic <mindtonic(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Windowed Postgres Query |
Date: | 2010-08-18 21:51:34 |
Message-ID: | b91fc4da-8bb1-443d-858a-05dc8e24c9e6@q22g2000yqm.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2010-08-18 21:58:14 | Re: plpgsql out parameter with select into |
Previous Message | Imre Horvath | 2010-08-18 15:00:17 | Re: plpgsql out parameter with select into |