SQL or Excel gurus... how to get rid of duplicates?

Discussion in 'OT Technology' started by driftwell, Jun 9, 2005.

  1. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    I want to create a query that will not show duplicates of a record, BUT WON'T DELETE them from the table

    I've researched commands such as KILLDUPS... but i believe those will delete them from the table... which is NOT what i'm trying to do.

    Here's my sql statement:

    SELECT
    inv_mast.item_id,
    inv_mast.default_product_group,
    inv_mast.item_desc,
    inv_mast.purchasing_weight,
    inv_mast.sales_pricing_unit_size,
    inv_loc.qty_on_hand,
    inventory_supplier.list_price

    FROM inv_mast, inv_loc, inventory_supplier
    WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
    AND inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid
    AND inv_mast.default_product_group = 'TRUCKLIT'
    AND inv_loc.qty_on_hand > '0'

    ORDER BY item_id



    and this gives my duplicates of certain records. Is there anyway to add a statement in there so the dups won't show?

    I'm also going to throw this into excel... so if there's a way in excel to get rid of dups... please let me know

    thanks
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    so you want to select from a table, but only show unique entries? No duplicates?
    SELECT DISTINCT is what you want.
     
  3. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    omg... i feel like an idiot.. i totally forgot about the "distinct" statement...

    thanks man..

    okay so just put SELECT DISTINCT at the very top right?
     
  4. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    Well, assuming your original query returns everything you need, but WITH duplicates, then yes, the "SELECT DISTINCT" instead of "SELECT" will yield the same results WITHOUT duplicates.
     
  5. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    can i specifiy which field i want to make distinct?
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    DISTINCT limits duplicate records - ie every field is identical. What you are looking for is GROUP BY
     
  7. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    yea... unfortunately our database won't delete duplicate part numbers if the prices are the same...

    so i can group by part number then.. cool thanks guys
     
  8. driftwell

    driftwell New Member

    Joined:
    Oct 7, 2003
    Messages:
    220
    Likes Received:
    0
    wait... i can't use group by along with order by? This is what i've got and it gives me a syntax area with the "group by" line

    SELECT DISTINCT
    inv_mast.item_id,
    inv_mast.default_product_group,
    inv_mast.item_desc,
    inv_mast.purchasing_weight,
    inv_mast.sales_pricing_unit_size,
    inv_loc.qty_on_hand,
    inventory_supplier.list_price

    FROM inv_mast, inv_loc, inventory_supplier
    WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
    AND inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid
    AND inv_mast.default_product_group = 'TRUCKLIT'
    AND inv_loc.qty_on_hand > '0'

    ORDER BY item_id
    GROUP BY item_id
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    The Group By also sorts based on the order of the items you are grouping by. In other words, drop the order by clause.

    It might be helpful if you posted the table layouts and some sample data since I don't see a Join statement anywhere either.
     
    Last edited: Jun 10, 2005
  10. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    The ORDER BY usually comes after the GROUP BY clause depending on the RDBMS you are using (what are you using? MS SQL? MySQL? Oracle?)

    5Gen: It appears the join is a 1 to 1 join being done in the WHERE clause: inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
    AND inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid

    I personally prefer the LEFT/INNER JOIN clauses myself since they're easier to read. But in the world of Oracle 8/9, the language syntax is not available.

    Also, you commented about group by doing the sorting as well. This is true, although I would consider it a little bit dangerous to assume it'll always do it. It may be unclear to someone looking at the SQL statement for the first time. Plus if you needed to sort by descending order, you'd have to toss in the ORDER BY clause anyways. Unless you could get away with GROUP BY [field] DESC or something like that (haven't tried it nor have I seen it but I guess that could be cool if its available).
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah I didn't even think to look for something other than a JOIN clause. But it will always sort it - I've never seen a query that doesn't. And yes, if you need to sort it in descending order, you need the order by clause, and yes it does go last.
     

Share This Page