Обсуждение: Need help with complex query

Поиск
Список
Период
Сортировка

Need help with complex query

От
Yasir Malik
Дата:
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 salesgroup 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.



Re: Need help with complex query

От
Yasir Malik
Дата:
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.
> >
>
>