Re: Need help with complex query - Mailing list pgsql-sql

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@rodos.fzk.de>
> To: ymalik@cs.stevens-tech.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.
> >
>
>