title: Acumatica: Retrieving first record in a view for a display field - extraneous Order Bys
body: <p>We have created a screen and graph that stores custom information about a given serial number for an inventory item (INItemLotSerial).</p>
  
  <p>I'm looking to be able to display the current location of the serial number, based on the location of the most recent transaction.  (Ideally I'd also like to be able display if the serial number is currently in inventory, but that's probably a different question.)</p>
  
  <p>Here is my view on the graph:</p>
  
  <pre><code>public PXSelect&lt;INTranSplit, Where&lt;INTranSplit.lotSerialNbr, Equal&lt;Optional&lt;INItemLotSerial.lotSerialNbr&gt;&gt;, 
      And&lt;INTranSplit.inventoryID, Equal&lt;Optional&lt;INItemLotSerial.inventoryID&gt;&gt;&gt;&gt;, OrderBy&lt;Desc&lt;INTranSplit.createdDateTime&gt;&gt;&gt; InventoryLocation;
  </code></pre>
  
  <p>And my field on the page:</p>
  
  <pre><code>&lt;px:PXSegmentMask Enabled="False" AllowEdit="False" runat="server" ID="CstPXSegmentMask5" DataField="InventoryLocation.LocationID" &gt;&lt;/px:PXSegmentMask&gt;
  </code></pre>
  
  <p>I'm expecting the field to grab the first record and ignore the rest.</p>
  
  <p>However, when I look at the generated SQL in a SQL Trace, Acumatica appears to be adding its own Order By fields:</p>
  
  <pre><code>exec sp_executesql N'SELECT [INTranSplit].[DocType], [INTranSplit].[TranType], [INTranSplit].[RefNbr], [INTranSplit].[LineNbr], [INTranSplit].[POLineType], [INTranSplit].[TransferType], [INTranSplit].[ToSiteID], [INTranSplit].[ToLocationID], [INTranSplit].[SplitLineNbr], [INTranSplit].[TranDate], [INTranSplit].[InvtMult], [INTranSplit].[InventoryID], [INTranSplit].[SubItemID], [INTranSplit].[CostSubItemID], [INTranSplit].[CostSiteID], [INTranSplit].[SiteID], [INTranSplit].[LocationID], [INTranSplit].[LotSerialNbr], [INTranSplit].[ExpireDate], [INTranSplit].[Released], [INTranSplit].[UOM], [INTranSplit].[Qty], [INTranSplit].[BaseQty], [INTranSplit].[MaxTransferBaseQty], [INTranSplit].[OrigPlanType], [INTranSplit].[IsFixedInTransit], [INTranSplit].[PlanID], [INTranSplit].[TotalQty], [INTranSplit].[TotalCost], [INTranSplit].[AdditionalCost], ( CASE WHEN  ( [INTranSplit].[TotalQty] = .0) THEN  .0 ELSE  ( [INTranSplit].[TotalCost] /  [INTranSplit].[TotalQty]) END), [INTranSplit].[CreatedByID], [INTranSplit].[CreatedByScreenID], [INTranSplit].[CreatedDateTime], [INTranSplit].[LastModifiedByID], [INTranSplit].[LastModifiedByScreenID], [INTranSplit].[LastModifiedDateTime], [INTranSplit].[tstamp], [INTranSplit].[UsrQtyForQC], [INTranSplit].[UsrQtyCoded], [INTranSplit].[UsrQtyCompleted] FROM INTranSplit INTranSplit WHERE (INTranSplit.CompanyID = 2) AND  [INTranSplit].[LotSerialNbr] = @P0 AND [INTranSplit].[InventoryID] = @P1 
  ORDER BY [INTranSplit].[DocType], [INTranSplit].[RefNbr], [INTranSplit].[LineNbr], [INTranSplit].[SplitLineNbr], [INTranSplit].[CreatedDateTime] DESC OPTION(OPTIMIZE FOR UNKNOWN) /* IN.21.00.00 */',N'@P0 nvarchar(100),@P1 int',@P0=N'EOSC52270005',@P1=16067
  </code></pre>
  
  <p>which are causing a different record to be returned first, rather than the most recent one.</p>
  
  <p>How do I convince Acumatica to run the BQL I'm asking for and drop the extra order by fields?  Or is there an entirely different approach to displaying the most recent transaction location that would be preferable?</p>