title: Retrieving only one record for each entity in a SQL table
body: <p>I have a complex join query which results in around 12000 items which is as follows</p>
  
  <pre><code>SELECT        a.ID AS 'DealID', a.TradeDate, c.COMPANYNAME AS 'Seller Company', a.SellCommission, h.BROKER_FULLNAME AS 'Seller Trader', 
                           j.DisplayName AS 'Seller Broker', d.COMPANYNAME AS 'Buyer Company', a.BuyCommission, g.BROKER_FULLNAME AS 'Buyer Trader', 
                           i.DisplayName AS 'Buyer Broker', e.PRODUCT_NAME, f.TYPE_DESC AS 'Quantity Type', f.NBR_OF_GALLONS AS 'Quantity Multiplier', a.ContractVolume, 
                           a.TotalVolume, a.DeliveryPoint, a.Price, a.ContractStart, a.ContractEnd
  FROM            Confirmations AS a WITH (nolock) LEFT OUTER JOIN
                           COMPANY AS c WITH (nolock) ON c.COMPANY_ID = a.SellCompany LEFT OUTER JOIN
                           COMPANY AS d WITH (nolock) ON d.COMPANY_ID = a.BuyCompany LEFT OUTER JOIN
                           BIOPRODUCTTYPES AS e WITH (nolock) ON e.ID = a.ProductID LEFT OUTER JOIN
                           BIO_QUANTITY_TYPE AS f WITH (nolock) ON f.ID = a.QuantityTypeID LEFT OUTER JOIN
                           COMPANYBROKER AS g WITH (nolock) ON g.COMPANYBROKER_ID = a.BuyTrader LEFT OUTER JOIN
                           COMPANYBROKER AS h WITH (nolock) ON h.COMPANYBROKER_ID = a.SellTrader LEFT OUTER JOIN
                           Users AS i WITH (nolock) ON i.ID = a.BuyBroker LEFT OUTER JOIN
                           Users AS j WITH (nolock) ON j.ID = a.SellBroker
  WHERE        (a.TradeDate &gt;= '2011-04-01 00:00:00') AND (a.TradeDate &lt;= '2013-10-25 00:00:00')
  ORDER BY a.TradeDate
  </code></pre>
  
  <p>But instead of all 12000 records I just want one record which is the most recent record for each "Seller Trader"(currently above query returns all the deals done by a seller trader) which probably returns around 300 records.</p>