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] -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
