Error when using ROW_COUNT alias in where clause

maximus1974
maximus1974 used Ask the Experts™
on
I have the following statement where I am trying to filter by RN = 1. I am using row_count. However, I keep receiving invalid column RN even though it is a column with the results.
Screenshot attached.

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

Open in new window

SCREENSHOT
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
"select distinct" + "group by" + "row_number()" all within a single select statement.
Mmmm.

The GROUP BY clause produces UNIQUE ROWS, but can do a great deal more than that
SELECT DISTINCT produces UNIQUE ROWS, and does nothing more than that
see: Select Distinct is returning duplicates ...
You do not need both in a single query. As you not using any aggregate functions "select distinct" would suffice.

Having said that however, it is better to avoid costly operations such as select distinct  being performed over multiple joins - and maybe your row_number() is designed to help remove unwanted rows.

Any function that requires use of OVER() cannot be directly referenced in the where clause as window functions are performed as part of a select clause and that is after the from/where clauses have finished.

Something like this: 
SELECT DISTINCT
    *
FROM (
    SELECT
        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
) 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

Open in new window


--------- regarding "select distinct" ----------

Why I Hate DISTINCT

Some Simple SQL Rules to Live By, DISTINCT is *usually* bad

A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go.  But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !!  Step back, look at your joins, and re-write your query properly.  Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates".  Not good.  (Side note: If you are a DISTINCT abuser, try adding meaningful primary keys to your tables).
http://webbtechsolutions.com/2009/07/24/the-effects-of-distinct-in-a-sql-query/
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

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

Open 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 ?
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!

Find out now!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

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

Open 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

Open in new window

However we can only guess if this is required in your where clause or not.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Having just posted in your other question at : https://www.experts-exchange.com/questions/29121774/Need-to-select-only-the-first-row-of-each-unique-value.html

I think the problem is you cannot use a generated column from the select immediately within the same query. Those generated columns (like row_number() ) need to somehow exist before you start using / referring to them by name.

As Paul points out above, it can be via a CTE or via Derived Table (subquery). Both are essentially a named query that you can select from - all as a single query script.

And from having read your other question, I now believe that WHERE clause needs to remain where it was and my caution about using "OR" was not exactly applicable in this instance, because that is not the intended behaviour anyway.

So, please have a look at : 
;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 *
  FROM cte 
  WHERE RN = 1
  ORDER BY SOURCE_AK

Open in new window


I do like the Common Table Expression  : https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 

Basically it enables you to name a query and then select from that name and very handy in these cases where you are creating columns, and want to use those columns - like row_number()

The basic structure is : 
;WITH CTE_NAME AS
(
   <your target query goes here - with any tweaks > 
) 
   SELECT <whatever columns or * for all columns>
   FROM CTE_NAME

Open in new window

And again as Paul rightly points out above, you could select from a derived table (a subquery).  

Except I say "po-tay-toh"

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

Author

Commented:
Thank you all for your help, greatly apprecaited. How can I place the statement using CTE in a view for a user to access?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

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

Open in new window

And please note the embedded comments in the code snippet above.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

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

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