Re: Best way to do this query?
From: Neo <neo55592_at_hotmail.com>
Date: 14 Jan 2007 14:14:24 -0800
Message-ID: <1168812864.743973.72670_at_a75g2000cwd.googlegroups.com>
(new 'c1 'variable) (set c1 refersTo nothing)
(new 'n1 'variable) (set n1 refersTo nothing)
(set (getElemLast (change (getSeq c1 refersTo *)
) Received on Sun Jan 14 2007 - 23:14:24 CET
Date: 14 Jan 2007 14:14:24 -0800
Message-ID: <1168812864.743973.72670_at_a75g2000cwd.googlegroups.com>
> Consider the following schema: > Invoices: {invoiceid, customerid, date, amount} > > date of every customer's most recent invoice: > select customerid, max(date) from Invoices group by customerid > > But suppose I also want to know > the id and amount of that particular invoice?
Following is possible with dbd which returns:
john amount 44.99 invoice inv_2
mary amount 64.99 invoice inv_4
(new 'customer)
(new 'amount)
(new 'inv_1 'invoice)
(set+ (it) customer 'john)
(set+ (it) date '20070101)
(set+ (it) amount '34.99)
(new 'inv_2 'invoice)
(set+ (it) customer 'john)
(set+ (it) date '20070102)
(set+ (it) amount '44.99)
(new 'inv_3 'invoice)
(set+ (it) customer 'mary)
(set+ (it) date '20070103)
(set+ (it) amount '54.99)
(new 'inv_4 'invoice)
(set+ (it) customer 'mary)
(set+ (it) date '20070104)
(set+ (it) amount '64.99)
(; The following expression return:
john amount 44.99 invoice inv_2 mary amount 64.99 invoice inv_4)
(new 'c1 'variable) (set c1 refersTo nothing)
(new 'n1 'variable) (set n1 refersTo nothing)
(set (getElemLast (change (getSeq c1 refersTo *)
(get customer instance *))) amount (max (get (getElemLast (change (getSeq n1 refersTo *) (& (get invoice instance *) (get * customer (get c1 refersTo *)) ) ) ) amount * ) ) invoice (get n1 refersTo *)
) Received on Sun Jan 14 2007 - 23:14:24 CET