Need to select only the first row of each unique value

maximus1974
maximus1974 used Ask the Experts™
on
Need to select only the first row of each unique value. As you can see from the attached screenshot, I only want to display the record STM_AUTO_KEY = 42962. In other words, I want to see only the lowest record of STM_AUTO_KEY.

SELECT DISTINCT 
                         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
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

Open in new window

Capture.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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) 

Open in new window

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

Commented:
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.

Author

Commented:
Lowest meaning the lowest STM_AUTO_KEY value belonging to SOURCE_AK. Pardon my ignorance, where would I place ROW_NUMBER within my statement?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Keep receiving error. ROW_NUMBER not working.
Capture.JPG
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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"

Open 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

Open in new window

Author

Commented:
Sorry but still getting error with parenthesis. Error attached.
Capture.JPG
Co-Founder and Chief Architect
Top Expert 2016
Commented:
You have ROW_NUMBER() in the FROM clause rather than the SELECT clause.

Author

Commented:
Sorry, that was silly of me. Thank you for your help.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
>> 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

Open 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....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial