On Monday, September 10, 2012 11:21:58 AM UTC-7, abhijit wrote: > > Hi Jeremy, > > I've managed to get rid of the error. I installed the following gems - > 'sqlite3' (1.3.6), 'sqlite3-ruby' (1.3.3) and 'sequel' (3.39.0) > > However, the grouping doesn't seem to work. My code - > db = Sequel.sqlite > db.create_table? :timesheets do > primary_key :id > String :project > Date :start > end > > table = db[:timesheets] > table.insert(:project => 'AAA', :start => DateTime.new(2008, 7, 8, 9, 00)) > table.insert(:project => 'AAA', :start => DateTime.new(2008, 7, 25, 10, > 00)) > table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 1, 9, 00)) > table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 10, 13, > 00)) > table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 20, 17, > 00)) > > puts table.group(:project).all.to_yaml > # --- > # - :project: AAA > # :start: 2008-07-25 15:30:00 +05:30 > # :id: 2 > # - :project: BBB > # :start: 2011-05-20 22:30:00 +05:30 > # :id: 5 > > puts table.group(Sequel.extract(:year, :start)).all.to_yaml > # --- > # - :project: BBB > # :start: 2011-05-20 22:30:00 +05:30 > # :id: 5 > > It seems only to give me details of the last inserted record. > Why? What am I doing wrong? > > Your query is invalid. In SQL, when you use GROUP, you can only SELECT expressions that you are GROUPing on and aggregate function calls. SQLite allows your invalid query and just returns values for an arbitrary row. Stricter databases (e.g. PostgreSQL) would raise an error if given that query. For a more detailed explanation, consult a good SQL reference book.
You should probably use select_group instead of group, and then use select_append to add any aggregate function calls you need. Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/vRw3oUcFrKMJ. 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/sequel-talk?hl=en.
