Select First instance of set per 1

[MsHart2u]

I am attempting to retrieve the 1st instance this rule is met for each prodcode. The broken query is as follows:

 SELECT p.ProdCode, 
Min(Month([post])) AS Mth, 
p.Title
FROM tbl_invtrans d 
RIGHT JOIN tbl_product p ON d.prodcode = p.ProdCode
WHERE (((p.Pub_Date)>'2004-12-01') 
      AND ((d.trans_type)='Sales'))
GROUP BY p.ProdCode, Month([post]), p.Title
HAVING (((Sum(d.quantity))>5))
ORDER BY p.ProdCode, Month([post])

CURRENT OUTPUT LOOKS LIKE THIS:
0613 6 (Spanish) Berlin 2/e
0613 8 (Spanish) Berlin 2/e
0613 9 (Spanish) Berlin 2/e
0613 10 (Spanish) Berlin 2/e
0740 9 Cantonese Phrase 4/e
0740 10 Cantonese Phrase 4/e
0740 11 Cantonese Phrase 4/e
085X 6 Kyoto 3/e
085X 7 Kyoto 3/e
085X 8 Kyoto 3/e
085X 9 Kyoto 3/e
085X 10 Kyoto 3/e
085X 11 Kyoto 3/e

Please clue me in as to how I can get this:
DESIRED OUTPUT
0613 6 (Spanish) Berlin 2/e
0740 9 Cantonese Phrase 4/e
085X 6 Kyoto 3/e

Forever in your debt...

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[SQLDenis]

Take out Month([post]) from the Group by

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[MsHart2u]

Thank you for the feedback. Unfortunatley, removing this from the group gets me one record, the 1st month, but I need the 1st month where the quantity total is above 5.

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[SQLDenis]

What about this?
select x.ProdCode,Min(x.mth),x.title
from(
SELECT p.ProdCode,
Min(Month([post])) AS Mth,
p.Title
FROM tbl_invtrans d
RIGHT JOIN tbl_product p ON d.prodcode = p.ProdCode
WHERE (((p.Pub_Date)>'2004-12-01')
AND ((d.trans_type)='Sales'))
GROUP BY p.ProdCode, Month([post]), p.Title
HAVING (((Sum(d.quantity))>5))
ORDER BY p.ProdCode, Month([post]))x
group by x.ProdCode,x.title

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

I had trouble with the ORDER BY (error reported that ORDER BY couldn't be done in that situation) inside of the select for the table variable x. Once I removed it, then the query returned valid results for me.

 

[SQLDenis]

That's right the order by should go to the outside
Since i didn't run this I didn't get the error

select x.ProdCode,Min(x.mth),x.title
from(
SELECT p.ProdCode,
Min(Month([post])) AS Mth,
p.Title
FROM tbl_invtrans d
RIGHT JOIN tbl_product p ON d.prodcode = p.ProdCode
WHERE (((p.Pub_Date)>'2004-12-01')
AND ((d.trans_type)='Sales'))
GROUP BY p.ProdCode, Month([post]), p.Title
HAVING (((Sum(d.quantity))>5)))x
group by x.ProdCode,x.title
ORDER BY p.ProdCode, Month([post])

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[MsHart2u]

SQLDenis,
Bless you, thank you!
Thank you. Bless You!!
This is perfectly the help I needed!!!

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb