SQL - Only show results for one column in first instance of a duplicated record

Question

I am finding it difficult to explain exactly what I am trying to achieve so I think it best to show a visual representation.

Example of how my query results currently look

Example of how I want the results to look

The report I am running shows a list of every product within orders. Each product has its own cost assigned to it. Another column is a delivery charge, but this is a charge assigned to the order; not individual products. I want to be able to show the delivery charge against the first product in each order ONLY.

I have attempted, for far too long, to try and find an answer to this query but have had no luck. I don't know if it is even possible so assistance of any sort, or even just being pointed in the right direction, would be of great help.

Thanks

EDIT.

If it helps here is my query:

SELECT dbo.Orders.EncryptedOrderId,
       dbo.OrderProduct.ProductID,
       dbo.OrderProduct.QuantityPerRecipient,
       dbo.OrderProduct.NumRecipients,
       dbo.OrderProduct.TotalQuantity,
       dbo.DocType.Name AS [Product Type],
       dbo.ProductGroup_Culture.Name AS [Product Group],
       RIGHT(CatalogNo, CHARINDEX('_', REVERSE('_' + CatalogNo)) -1) AS [HamptonsType],
       FORMAT(dbo.Orders.DateOrderCreated, 'dd/MM/yyyy HH:mm:ss') AS 'DateOrderCreated',
       CAST(REPLACE(dbo.Orders.ClearingResult, 'utf-8', 'utf-16') AS XML ).value('(/UserData//CostCenter/node())[1]', 'nvarchar(max)') AS [Cost Center], 
       dbo.Users.FirstName, 
       dbo.Users.LastName, 
       dbo.Users.CompanyName AS [Branch Name], 
       dbo.Users.Department AS Subsidiary, 
       dbo.Users.Custom1, 
       dbo.Users.Custom2, 
       dbo.Users.Custom3, 
       dbo.Users.Custom4, 
       dbo.Users.Custom5, 
       dbo.OrderProduct.TotalPrice,
       dbo.Orders.ShippingCharges,
       dbo.OrderProduct.OrderProductID, 
       dbo.FileSubmissionDocument.OriginalFileType, 
       COALESCE (dbo.FileSubmissionDocument.Title, dbo.Product_Culture.Name) AS [Product Name],
       OPDV.FriendlyValue AS 'BCard Recipient'
FROM dbo.DocType
INNER JOIN dbo.Doc
    ON dbo.DocType.DocTypeID = dbo.Doc.DocTypeID
INNER JOIN dbo.OrderProduct
    ON dbo.Doc.ProductID = dbo.OrderProduct.ProductID
LEFT JOIN dbo.Product
    ON dbo.Product.ProductID = dbo.Doc.ProductID
LEFT JOIN dbo.ProductGroupMembership
    ON dbo.ProductGroupMembership.ProductID = dbo.Doc.ProductID
LEFT JOIN dbo.ProductGroup_Culture
    ON dbo.ProductGroup_Culture.ProductGroupID = dbo.ProductGroupMembership.ProductGroupID
INNER JOIN dbo.Orders
    ON dbo.OrderProduct.OrderID = dbo.Orders.OrderID
INNER JOIN dbo.Users
    ON dbo.Orders.UserID = dbo.Users.UserID
INNER JOIN dbo.Product_Culture
    ON dbo.OrderProduct.ProductID = dbo.Product_Culture.ProductID 
INNER JOIN dbo.Store_Culture
    ON dbo.Store_Culture.StoreID = dbo.Users.AssignedToStoreID FULL OUTER
JOIN dbo.FileSubmissionDocument
    ON dbo.OrderProduct.OrderProductID = dbo.FileSubmissionDocument.SubOrderProductID - 1
LEFT JOIN (SELECT OP.OrderProductID,
                  OP.DialID,
                  OP.FriendlyValue
           FROM OrderProductDialValue OP
           LEFT JOIN Dial DI ON DI.DialID = OP.DialID
           LEFT JOIN OrderProduct OT ON OT.OrderProductID = OP.OrderProductID
           LEFT JOIN Product PR ON PR.ProductID = OT.ProductID
           WHERE PR.ExternalID = 'BCName'
           AND DI.UProduceDialName = 'Name') OPDV ON OPDV.OrderProductID = dbo.OrderProduct.OrderProductID
WHERE ('@CUSTOMERNAME' is null
        OR '@CUSTOMERNAME' = ''
        OR dbo.Store_Culture.Name LIKE '%' + '@CUSTOMERNAME' + '%')
        AND dbo.OrderProduct.IsDraft = 0
        AND dbo.Orders.IsCart=0
        AND dbo.Orders.IsSaveForLater=0
        AND (('@DATE' <= dbo.Orders.DateOrderCreated)
        OR ('@DATE' IS NULL)
        OR ('@DATE'=''))
        AND ((DATEADD(day, 1, '@DATE') >= dbo.Orders.DateOrderCreated)
        OR ('@DATE' IS NULL)
        OR ('@DATE'=''))
AND dbo.Users.LastName NOT LIKE '%TEST%'
ORDER BY  dbo.Orders.OrderID DESC, dbo.OrderProduct.OrderProductID DESC

The query runs through a reporting system on an online portal so the values that show as @CUSTOMERNAME or @DATE are variables based on values given at the time when the report is run.

Answer 1

this may help you

select orderid,
    productid,
    productvalue,
    case ROW_NUMBER() over (partition by orderid order by orderid)
    when 1 then deliverycharge
    else null end as 'deliverycharge'
     from ........

Answer 2

I assume your query looks like

    select orderID
         , productID
         , productValue
         , DeliveryCharge
      from test_t
  order by orderID
         , productValue desc
         ;

and that you want delivery charges listed for the most expensive product of each order.

If supported by your rdbms, you can use the analytic RANK function

    select orderID
         , productID
         , productValue
         , DeliveryCharge
         , CASE RANK() OVER ( PARTITION BY orderID ORDER BY productValue DESC ) WHEN 1 THEN DeliveryCharge ELSE NULL END r
      from test_t
  order by orderID
         , productValue desc
         ;

If your rdbms does not support RANK, you can emulate it using a left join with a suitably aggregated copy of your table:

    select t.orderID
         , t.productID
         , t.productValue
         , rt.mdc DeliveryCharge
      from test_t t
 left join (
                select orderID
                     , max(productValue)    mp
                     , max(DeliveryCharge)  mdc
                  from test_t
              group by orderID
           ) rt
        on (
                 rt.orderID = t.orderID 
             AND rt.mp      = t.productValue 
           )
  order by orderID
         , productValue desc
         ;

The tacit assumption is that the delivery charge is by order (which seems reasonable as you wouldn't want to selectively drop it otherwise, right ?).

Moreover, both solutions will produce multiple rows containing the delivery charge per order if that order contains multipleproducts with the same productValue.

Tested on oracle 12c1;