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? -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Aug 18, 10:44 pm, Mindtonic <mindto...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.