Need help with complex query

Поиск
Список
Период
Сортировка
Искать
От
Yasir Malik
Тема
Need help with complex query
Дата
Msg-id
Pine.SGI.4.51.0307071157340.3939805@guinness.cs.stevens-tech.edu
Список
Дерево обсуждения
Need help with complex query Yasir Malik <ymalik@cs.stevens-tech.edu>
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.


В списке pgsql-sql по дате отправления
От:
Дата:
От: markus brosch
Дата:
FAQ