Error when using ROW_COUNT alias in where clause

Eyeballeth thy SQL Query Order of Execution.   The reason you are getting that error is because the query engine processes the WHERE clause before the SELECT clause, so that the time of WHERE processing it has no idea what RN is because it hasn't been defined yet.

This can be confusing at first as the visual order of a query, SELECT - FROM with JOINs - WHERE - GROUP BY - ORDER BY - HAVING, is different from how the query engine processes it.

A common way around this is to throw your whole query minus the WHERE RN = 1 part into a subquery, then in a 'wrapper' main query SELECT all the columns from your subquery, and there you can use the WHERE RN = 1 as it will exist.

i.e.

SELECT a.{all columns}
FROM ( 
   -- your query here, minus the WHERE RN = 1 part ) a
WHERE a.RN = 1

Select allOpen in new window

I would be trying something like (a CTE query) :

;with CTE as
(
  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,
                         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
   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') OR RN = '1' 
   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   -- moved to final select, otherwise have to use TOP in the initial select
)
  SELECT DISTINCT *
  FROM cte 
  WHERE ((SOURCE_TABLE = 'SMH') AND (ORIG_VALUE = 'PICKING') AND (NEW_VALUE = 'PACKING')) OR RN = '1'
  ORDER BY SOURCE_AK

Select allOpen in new window

Be careful as to using OR in the where clause, make very sure you use brackets to force the correct conditions. I have made an assumption above for that 'OR' in the where clause.

Basic rule of thumb is you need to GROUP BY for aggregations. Use DISTINCT for unique rows. You dont need both together....

Using a CTE (Common Table Expression) is a way of giving a name to a query, in this case, I named it (unimaginatively) "CTE". Then you can subsequently select from the name you gave it -
 a bit like a subquery / derived table (except we start by giving it a name).. Extremely useful when generating new columns as part of a query.

Does that make sense ?

LOL "you say pot-a-to I say po-ta-toh"

If you define a derived table conventionally, or via a CTE it is the effectively the same

select * 
from (
  some subquery here
          ) as D                << here is the declared alias

- v -

with D as (                     << here is the declared alias
   some subquery here
   )           
select * 
from D                       

Select allOpen in new window

Whilst not directly relevant to this question common tables expressions are needed for recursion, but optional otherwise.

It is a worthwhile point made regarding the OR in the where clause, using indentation can also help to make this clear:

SELECT DISTINCT
    *
FROM (
    SELECT
...
) d
WHERE (d.SOURCE_TABLE = 'SMH'
    AND d.ORIG_VALUE = 'PICKING'
    AND d.NEW_VALUE = 'PACKING'
      )
OR d.RN = '1'
ORDER BY
    d.SOURCE_AK

Select allOpen in new window

However we can only guess if this is required in your where clause or not.

Thank you all for your help, greatly apprecaited. How can I place the statement using CTE in a view for a user to access?

To create a view :

CREATE VIEW VW_AUDIT_TRAIL as
with CTE as
(
  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,
                         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
   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   -- moved to final select, otherwise have to use TOP in the initial select
)
  SELECT *  -- really should use column names, not select * , and if wanting to keep ORDER BY then make you select : select top 100 percent
  FROM cte 
  WHERE RN = 1  -- maybe comment this out so you can use RN< 3 or RN = 1 etc....
--  ORDER BY SOURCE_AK   -- order by is invalid in a view unless you use TOP in the sellect clause e.g. select top 100 percent 
GO

-- then to test

select * from VW_AUDIT_TRAIL
order by source_ak

Select allOpen in new window

And please note the embedded comments in the code snippet above.

You do NOT have to use a cte for this query. There is NO technical advantage here.

What is important is that row_number is part of a subquery, then you may filter by that calculation. The subquery may be in the traditional form or it can be a cte. Both do exactly the same thing in this query.