Greg Stark <[EMAIL PROTECTED]> writes:
> What would be useful is something like
> SELECT item_id,
>first(price) as min_price, first(store_id) as min_store,
>avg(price) as avg_price,
>last(price) as max_price, last(store_id) as min_store,
>count(distinct store_id)
Greg Stark <[EMAIL PROTECTED]> writes:
> Manfred Koizar <[EMAIL PROTECTED]> writes:
>
> > Greg, we already have this feature, just the syntax is a bit different :-)
> >
> >SELECT DISTINCT ON (item_id) item_id,
> > price AS lowest_price,
> > store_id AS lowest
On 17 Jan 2003 19:08:06 -0500, Greg Stark <[EMAIL PROTECTED]> wrote:
>Would this query be efficient if there's an index on item_id, price ? That is,
>would it know to do an index scan
Yes, at least to avoid the sort step.
> and be able to skip to the next item_id in
>the index as soon as a price
Manfred Koizar <[EMAIL PROTECTED]> writes:
> Greg, we already have this feature, just the syntax is a bit different :-)
>
>SELECT DISTINCT ON (item_id) item_id,
> price AS lowest_price,
> store_id AS lowest_price_store
> FROM items_for_sale
>
Greg Stark <[EMAIL PROTECTED]> writes:
>> select min(column1),lookup_min(column1,column2) from tab
One small problem is that we only support single-argument aggregates.
As of 7.3 this is no longer wired into the system catalog layout, but
it's still wired into various internal datastructures. Any
On 17 Jan 2003 15:12:58 -0500, Greg Stark <[EMAIL PROTECTED]> wrote:
>SELECT item.*, store.*, x.lowest_price
> FROM item, store, (
> SELECT item_id,
> min(price) AS lowest_price,
> lookup_min(price,store_id) AS lowest_price_store
> FROM items_for_sale
>
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> On Fri, Jan 17, 2003 at 13:39:11 -0500,
> Greg Stark <[EMAIL PROTECTED]> wrote:
> >
> > So it would be possible to say for example:
> >
> > select min(column1),lookup_min(column1,column2) from tab
> >
> > to do the equivalent of:
> >
> > select
On Fri, Jan 17, 2003 at 13:39:11 -0500,
Greg Stark <[EMAIL PROTECTED]> wrote:
>
> So it would be possible to say for example:
>
> select min(column1),lookup_min(column1,column2) from tab
>
> to do the equivalent of:
>
> select column1,column2 where column1=(select min(column1) from tab) limit
I have an idea for an aggregate function (actually a pair) that would be very
useful. It's something I've wanted very frequently with Oracle and other
databases and while it's possible to implement in SQL it's hard to do
efficiently. Whereas it would be really easy for the database to do it
effici
