Eliminate Duplicate Records

  • I am trying to get unique records for each item_id. Sample data is:

    Order_no Item_id UOM Date_Created Commission_cost New_Sell_price2

    1022220BS3399 CASE 2009-01-16 29.16000000040.094999

    1022183BS3399 EACH 2009-01-15 2.6730000004.110000

    1022143BS3399 EACH 2009-01-16 2.6730000003.820000

    1022127BS3399 CASE 2009-01-14 29.16000000040.094999

    1021697BS3399 EACH 2009-01-08 2.6730000004.620000

    1021598BS3399 EACH 2009-01-07 2.6730000004.110000

    1021514BS3399 CASE 2009-01-06 32.07600000044.280000

    1021513BS3399 CASE 2009-01-06 32.07600000044.280000

    1021512BS3399 EACH 2009-01-06 2.6730000003.690000

    1021510BS3399 EACH 2009-01-06 2.6730000003.690000

    I want the record with the highest Order_no for each Item_id for each UOM. In the sample data I only want the first 2 records. I created a view (script below) to join to my main query and I'm getting a zillion records! Can you help?

    I have a script:

    IF OBJECT_ID ('eek_custlist_special_view', 'V') IS NOT NULL

    DROP VIEW eek_custlist_special_view ;

    GO

    create view eek_custlist_special_view

    as

    select distinct

    l.order_no,

    i.item_id,

    l.unit_of_measure,

    l.date_created,

    l.commission_cost,

    --was c_what_if

    --New Sell Price2 This will be the displayed price all prices

    CASE when ((100 * inventory_supplier.cost * 1.1) / (100.00000001 - case l.unit_price

    WHEN 0 THEN

    1 --avoid divide by 0 error

    ELSE

    (l.unit_price - l.commission_cost) / l.unit_price * 100

    END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) ) > l.unit_price

    then

    ((100 * inventory_supplier.cost * 1.1) / (100.00000001 -

    CASE l.unit_price

    WHEN 0 THEN

    1 --avoid divide by 0 error

    ELSE

    (l.unit_price - l.commission_cost) / l.unit_price * 100

    END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) )

    else

    l.unit_price

    end New_Sell_Price2

    FROM oe_line l

    INNER JOIN oe_hdr h ON (h.order_no = l.order_no) AND (COALESCE(h.order_type, 0) <> 1706)

    INNER JOIN inv_mast i ON (i.inv_mast_uid = l.inv_mast_uid)

    INNER JOIN inv_loc il ON i.inv_mast_uid = il.inv_mast_uid and il.location_id = l.source_loc_id

    inner join inventory_supplier on inventory_supplier.inv_mast_uid = i.inv_mast_uid

    and inventory_supplier.supplier_id = il.primary_supplier_id

    join oe_hdr_salesrep on oe_hdr_salesrep.order_number = h.order_no

    and oe_hdr_salesrep.primary_salesrep = 'Y'

    join contacts on contacts.id = oe_hdr_salesrep.salesrep_id

    INNER JOIN address ON (address.id = h.customer_id)

    where l.delete_flag <> 'y' and i.item_id like 'bs%'

    and item_id = 'bs3399'

    Any help greatly appreciated!

  • Use something like this as the basis for the view.

    SELECT *

    FROM

    (SELECT MAX(OrderNO) as MaxOrder, itemID, UOM FROM SomeTable GROUP BY ItemID, UOM) Qualifying

    INNER JOIN SomeTable ON Qualifying.itemID = SomeTable.ItemID AND Qualifying.UOM = SomeTable.UOM AND Qualifying.MaxOrder = SomeTable.OrderNo

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply