Need to select only the first row of each unique value

ROW_NUMBER is your friend.  You can use:

ROW_NUMBER OVER (PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) 

Select allOpen in new window

(or in PARTITION BY, whatever distinguishes them), and then select only results where that value is = 1.

What makes it the "lowest" record? Is it because it's the "highest" STM_AUTO_KEY? if that's the case, then Dustin Saunders has your answer.

Lowest meaning the lowest STM_AUTO_KEY value belonging to SOURCE_AK. Pardon my ignorance, where would I place ROW_NUMBER within my statement?

Keep receiving error. ROW_NUMBER not working.
Capture.JPG

I'm sorry, I typo'd in my post.  ROW_NUMBER is a method and needs parenthesis ->  ROW_NUMBER()

ROW_NUMBER() OVER (PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) AS "RN"

Select allOpen in new window

You can add that into the Query where you have it now, and do something like:

;WITH result AS (
 <your whole current query>
)
SELECT * FROM result
WHERE RN=1

Select allOpen in new window

Sorry but still getting error with parenthesis. Error attached.
Capture.JPG

Sorry, that was silly of me. Thank you for your help.

>> Sorry, that was silly of me.

Absolutely not at all silly, and definitely no need to apologise....

It is our role as Experts to explain in such a way that you do understand / can use the suggestions.

Just to clarify Dustin's solution :

; with CTE as 
(  SELECT -- DISTINCT   -- dont think you need this if we are selecting by RN 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE,
ROW_NUMBER() OVER (PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY ) AS RN  
-- DESC in the 'order by' means descending sequence to get the highest first. I dont think that is what you are asking for - so leave it out for ASC (ascending = low to high) sequence....
  FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON dbo.AUDIT_TRAIL.SYSUR_AUTO_KEY = dbo.SYS_USERS.SYSUR_AUTO_KEY ON 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SMH_AUTO_KEY = dbo.AUDIT_TRAIL.SOURCE_AK
  WHERE        (dbo.AUDIT_TRAIL.SOURCE_TABLE = 'SMH') AND (dbo.AUDIT_TRAIL.ORIG_VALUE = 'PICKING') AND (dbo.AUDIT_TRAIL.NEW_VALUE = 'PACKING')
  GROUP BY dbo.AUDIT_TRAIL.SOURCE_AK, dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
                         dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, dbo.VIEW_SM_STOCK.CONDITION_CODE, 
                         dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.LOCATION_CODE, 
                         dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE
--ORDER BY SOURCE_AK
) SELECT * 
  FROM CTE 
  WHERE RN = 1
  ORDER BY SOURCE_AK

Select allOpen in new window

Which is pretty much the same in your other question....  https://www.experts-exchange.com/questions/29121813/Error-when-using-ROW-COUNT-alias-in-where-clause.html


Is there still a problem with either of these questions, or, you are now in a position to identify (and reward) Solutions.

If needing to understand the new "close" process, please read : http://support.experts-exchange.com/customer/portal/articles/2527982  you can reward multiple posts now days.

And if anything doesnt make sense, or, is confusing, then please say so. We are here to help.

But please do try the scripts the experts are posting and let us know....