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@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 - 16:14:24 CST