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.
