Re: Need help with complex query

Mr. Haller
That was what I was exactly looking for. The guys at
experts-exchange.com or any other website could come up with that answer.
I figured it out without using DISTINCT ON, but the solution was
horrendous. I have already turned in my assignment, however. Thank you so
much for your help. I hope to continue to learn from professionals like you.
Thank you so much,
Yasir

On Wed, 9 Jul 2003, Christoph Haller wrote:

> Date: Wed, 09 Jul 2003 16:46:43 +0200
> From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
> To: ymalik(at)cs(dot)stevens-tech(dot)edu
> Subject: Re: [SQL] Need help with complex query
>
> Yasir,
> You were quite close already.
> That's exactly what DISTINCT ON was invented for (non-standard
> compliant)
> select DISTINCT ON (cust, prod) cust, prod, mn, dy, yr, quant
> from (select cust, prod, min(quant)
> from sales
> group by cust, prod) as x(c, p, q), sales
> where cust = x.c and prod = x.p and quant = x.q order by cust, prod;
> cust | prod | mn | dy | yr | quant
> ---------+--------+----+----+------+-------
> Hamming | Milk | 3 | 15 | 1998 | 47
> Jones | Fruits | 3 | 31 | 2001 | 45
> Knuth | Bread | 2 | 21 | 1949 | 13
> Knuth | Jam | 12 | 21 | 2002 | 41
> Knuth | Milk | 2 | 29 | 2000 | 12
> Kruskal | Jelly | 11 | 30 | 1999 | 5
> (6 rows)
> Regards, Christoph
>
> >
> > Suppose I have a relation called sales with the following schema:
> > sales-Schema = (cust, prod, mn, dy, yr, quant, st)
> >
> > An instance of the relation would look something like this:
> > cust prod mn dy yr quant st
> > ==== ==== == == == ===== ==
> > Knuth Milk 02 29 2000 12 CA
> > Jones Fruits 03 31 2001 45 WY
> > Knuth Jam 12 21 2002 41 MN
> > Kruskal Jelly 11 30 1999 12 NJ
> > Hamming Milk 03 15 1998 47 GA
> > Knuth Milk 02 29 2000 12 CA
> > Kruskal Jelly 11 30 1999 5 NJ
> > Knuth Milk 06 23 2002 12 CA
> > Knuth Bread 02 21 1949 13 CA
> >
> > Note: The relation can have duplicate rows.
> >
> > Now, I want this query: For each customer-product combination, find
> the
> > minimum quantity sold along with its respective date. If there are
> more
> > than one minimum sales quantity for a customer product combination,
> print
> > only one of them. So the query should return the following:
> >
> > cust prod mn dy yr quant
> > ==== ==== == == == =====
> > Jones Fruits 03 31 2001 45
> > Knuth Jam 12 21 2002 41
> > Hamming Milk 03 15 1998 47
> > Kruskal Jelly 11 30 1999 5
> > Knuth Milk 06 23 2002 12
> > Knuth Bread 02 21 1949 13
> >
> > I wrote the following query:
> > select cust, prod, mn, dy, yr, quant
> > from (select cust, prod, min(quant)
> > from sales
> > group by cust, prod) as x(c, p, q), sales
> > where cust = x.c and prod = x.p and quant = x.q;
> >
> > And I got the following relation:
> > cust prod mn dy yr quant
> > ==== ==== == == == =====
> > Knuth Milk 02 29 2000 12
> > Jones Fruits 03 31 2001 45
> > Knuth Jam 12 21 2002 41
> > Hamming Milk 03 15 1998 47
> > Knuth Milk 02 29 2000 12
> > Kruskal Jelly 11 30 1999 5
> > Knuth Milk 06 23 2002 12
> > Knuth Bread 02 21 1949 13
> >
> > which is not what I want because the Knuth-Bread combination is
> repeated;
> > I only want one of them. I have tried many other variations of the
> query,
> > but the best I've done is something like this (by selection distinct
> > quantities out of the above table):
> > cust prod mn dy yr quant st
> > ==== ==== == == == ===== ==
> > Jones Fruits 03 31 2001 45 WY
> > Knuth Jam 12 21 2002 41 MN
> > Hamming Milk 03 15 1998 47 GA
> > Knuth Milk 02 29 2000 12 CA
> > Kruskal Jelly 11 30 1999 5 NJ
> > Knuth Milk 06 23 2002 12 CA
> > Knuth Bread 02 21 1949 13 CA
> >
> > Can anyone help me out? Thanks in advance.
> >
>
>