Need help on SQL

Database

Need help on SQL

Hi,

I have data like this in table.

Account Product_Key Week_Ending_Date Price_Amt
0001100067 7048 20/02/2011 5.00
0001100067 7048 13/03/2011 4.00
0001100067 7048 03/04/2011 5.00
0001100067 7048 08/05/2011 5.00
0001100067 7048 15/05/2011 5.00
0001100067 7048 05/06/2011 5.00
0001100067 7048 26/06/2011 5.00
0001100067 7048 03/07/2011 5.00
0001100067 7048 17/07/2011 5.00
0001100067 7048 24/07/2011 5.00

I need out put like this.

Account Product_Key Min(week_ending_date) Price_Amt
0001100067 7048 20/02/2011 5.00
0001100067 7048 13/03/2011 4.00
0001100067 7048 03/04/2011 5.00

Can you please help how to get this out put using SQL?
2 REPLIES
Enthusiast

Re: Need help on SQL

What are you trying to get? The rows with the earliest 3 week_ending_date's? If so, it's like this:

SELECT Account
,Product_Key
,Week_Ending_Date
,Price_Amt
FROM tablename
QUALIFY ROW_NUMBER() OVER (PARTITION BY Account, Product_Key
ORDER BY Week_Ending_Date ASC) <=3
ORDER BY 1,2,3;
Enthusiast

Re: Need help on SQL

Or,
SELECT TOP 3 ACCOUNT
,Product_Key
,Week_Ending_Date
,Price_Amt
FROM tablename
GROUP BY ACCOUNT,product_key,week_ending_date,price_amt
ORDER BY (week_ending_date) ASC;