asked on
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
;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
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.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
Whilst not directly relevant to this question common tables expressions are needed for recursion, but optional otherwise.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
However we can only guess if this is required in your where clause or not.
ASKER
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
And please note the embedded comments in the code snippet above.
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
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.
Open in new window