On Feb 16, 2:48 pm, Jason Thomas <[email protected]> wrote:
> Works but results in two queries. A standard query would be:
>
> SELECT date, min(value) as value from data_table
That's not standard SQL, though some databases may allow it. In
standard SQL, when using an aggregate function, all non-aggregate
columns must be grouped by. You can't just group by the date, as that
is not the type of query you want to do. You want the row where the
value is the minimum value, which would usually be done via a subquery
in standard SQL.
If that's the query you want:
DB[:data_table].select{[date, min(value).as(value)]}
The standard SQL way to do what you want is:
SELECT date, value FROM data_table WHERE value = (SELECT min(value)
FROM data_table)
Which in Sequel is:
DB[:data_table].filter(:value=>DB[:data_table].select{min(value)})
If multiple rows have the minimum value, you'll get multiple records,
but there are ways to deal with that.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-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/sequel-talk?hl=en.
