How to get second lowest value from a field based on couple of conditions to create Summary table

[vinod.excel]

Hi All,

I've a table with following fields
BU, Product, Sub-Product, Product_Cost, Discount

Lets assume there are multiple records with duplicates in (BU, Product, Sub-Product) and having different numbers at Product_Cost and Discount.

My requirement is to pull lowest Product_cost and Max Discount values for unique record combination (BU, Product, Sub-Product)
When lowest Product_cost is zero then need to get second lowest cost (i.e., when 1st lowest cost is zero), if all costs are zero then return zero only.

I've written query using group by clause on all fields with Min(Product_Cost), Max(Discount) - But this results includes zero at Product_Cost which I don't want if there is a number at Product_Cost > 0



Please guide me on how to achieve it in simple query and your response would be highly appreciated.

Thanks.
~Vins

[ranman256]

Ask for DMIN() where the total is NOT zero. You wont need the 2nd value.