How to limit the number of rows in select statement for a condition in sql server 2012

Question

Customer|Product_Code|List_Price|Unit_Price|Unit_Unit_Price|Unit_List_Price|
--------+------------+----------+----------+---------------+---------------+
3BEACHES|CA050608    |9.46      |9.46      |EACH           |EACH
3BEACHES|CA050608    |9.46      |9.46      |INNER          |EACH
3BEACHES|CA050608    |9.46      |9.46      |SHIPPER        |EACH

In my select statement I am joining 4 Views - Customer,Product,Price Book and List Price View and I want only 1 row for a combination of Customer and Product_Code, I am getting 3 rows as attached above because there are different values in Unit_Unit_Price field for some Products, it can be any one of "EACH" or "INNER" or "SHIPPER" but just 1.

I can't put a Where clause on any 1 Value(Each,Inner or Shipper) because for some Customer-Product_Code line it's just showing 1 row(Shipper or Each or Inner), so if I am using where clause on any 1 value of Unit_Unit_Price field then it's deducting the records which is not correct.

What I want: there are no repeating rows for Customer-Product_Code combination, I just need unique rows.

Code I am using:

SELECT TOP (100) PERCENT skh.ARCUS.IDCUST,
                         skh.ICITEM.ITEMNO AS product_code,
                         skh.ICITEM.CNTLACCT,
                         skh.v_list_price_each.price AS list_price,
                         skh.v_price_book.price,
                         skh.v_list_price_each.unit AS Expr2,
                         skh.v_price_book.price_unit
FROM skh.ARCUS
INNER JOIN skh.v_price_book ON skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN skh.ICITEM ON skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND skh.v_price_book.company = skh.ICITEM.AUDTORG
INNER JOIN skh.v_list_price_each ON skh.v_price_book.item_short = skh.v_list_price_each.item_short
WHERE (skh.ICITEM.INACTIVE = 0)
  AND (skh.ARCUS.SWACTV = 1)
ORDER BY skh.ARCUS.IDCUST

Answer 1

It is difficult to say without your current query and sample data, but looks like this is something you want

select t1.*
from 
(
select t.*,
    row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk
from 
 (your current query) t
) t1
where t1.rnk=1

Note: If your current query doesn't have any group by then you can plug in row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk in your current query select clause and filter it in outer query as the example above.

Answer 2

If you need to limit the number of rows returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb

Due you're using a TOP (X) expression, you could add an ORDER BY clause into the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            pb.ITEMNO AS product_code,
            pb.CNTLACCT,
            pb.price AS list_price,
            pb.price,
            pb.unit AS Expr2,
            pb.price_unit
FROM        skh.ARCUS
CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb
WHERE        skh.ARCUS.SWACTV = 1
--ORDER BY    skh.ARCUS.IDCUST

As it seems you're using this query within a VIEW (TOP (100) PERCENT), I'd suggest you to remove TOP and move ORDER BY to your SELECT view_name statement.