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

selectsql server

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

Best Answer

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.

Related Question