distinct records

instead of distinct, add group by to the end of your query:

SELECT Pallet, Site, ItemNumber, MAX(OnHandQuantity),...
.
.
.
GROUP BY Pallet, Site, ItemNumber, ...

Select allOpen in new window

I did with same results as posted

can you post the text of your query

I attached to query with group by and the results
query2.jpg

If you attach the text of your query instead of image I can rewrite it.

now only UDF04 makes your records duplicate. it depends on your logic, but you can remove UDF04 from group by clause and use MAX(UDF04) in select clause.

if it is not the result you are looking for, another option is to use partition by.

SELECT DISTINCT
                         Inventory.Pallet, Inventory.Site, Inventory.ItemNumber, Inventory.OnHandQuantity, Inventory.UDF04, Item.Description, Item.AlternateNumber,
                         Inventory.AllocatedOrder, Inventory.OrderNumber, Item.AlternateNumber2, ItemUnitOfMeasure.UnitOfMeasure, ItemUnitOfMeasure.EachCount
FROM            Inventory INNER JOIN
                         Item ON Inventory.ItemNumber = Item.ItemNumber INNER JOIN
                         PickingOrder ON Inventory.Site = PickingOrder.Site AND Inventory.AllocatedOrder = PickingOrder.OrderNumber INNER JOIN
                         ItemUnitOfMeasure ON Item.ItemNumber = ItemUnitOfMeasure.ItemNumber
WHERE        (Inventory.AllocatedOrder = @ordernumber) AND (Inventory.ItemNumber <> '011299CP') AND (ItemUnitOfMeasure.UnitOfMeasure = 'pallet') AND
                         (Inventory.ItemNumber = '546000j#')
GROUP BY Inventory.Pallet, Inventory.Site, Inventory.ItemNumber, Inventory.OnHandQuantity, Inventory.UDF04, Item.Description, Item.AlternateNumber,
                         Inventory.AllocatedOrder, Inventory.OrderNumber, Item.AlternateNumber2, ItemUnitOfMeasure.UnitOfMeasure, ItemUnitOfMeasure.EachCount

Start with this:

SELECT  i.Pallet,
        i.Site,
        i.ItemNumber,
        MAX(i.OnHandQuantity) MaxOnHandQuantity
FROM    Inventory i
        INNER JOIN Item t ON i.ItemNumber = t.ItemNumber
        INNER JOIN PickingOrder p ON i.[Site] = p.[Site] AND i.AllocatedOrder = p.OrderNumber
        INNER JOIN ItemUnitOfMeasure m ON t.ItemNumber = m.ItemNumber
WHERE   i.AllocatedOrder = @ordernumber
        AND i.ItemNumber <> '011299CP'
        AND m.UnitOfMeasure = 'pallet'
        AND i.ItemNumber = '546000j#'
GROUP BY i.Pallet,
        i.Site,
        i.ItemNumber

Select allOpen in new window

And add columns as needed.  Just understand that they need to be either added as an aggregate function or added to the GROUP BY.  This last could cause more rows than you need.