asked on

SQL Statement with multiple tables need the last record returned

I have the following statement but need only the last or most current record only based off of assethistoryid for each Asset.

How do I construct the query based off the following query

SELECT        dbo.AssetHistory.AssetHistoryID, dbo.AssetHistory.AssetID, dbo.Asset.Asset, dbo.Customers.CustomerName, dbo.AssetHistory.CreatedOn, 
                         dbo.AssetMoveTo.MoveTitle, dbo.Stores.StoreNumber, dbo.AssetHistory.CreatedBy, dbo.TerminalName.TerminalName, dbo.AssetHistory.Reference, 
                         dbo.AssetHistory.Tracking, dbo.AssetHistory.Carrier, dbo.AssetHistory.Notes
FROM            dbo.AssetHistory INNER JOIN
                         dbo.Asset ON dbo.AssetHistory.AssetID = dbo.Asset.AssetID INNER JOIN
                         dbo.Customers ON dbo.AssetHistory.CustomerID = dbo.Customers.CustomerID AND dbo.Asset.CustomerID = dbo.Customers.CustomerID INNER JOIN
                         dbo.AssetMoveTo ON dbo.AssetHistory.AssetMoveID = dbo.AssetMoveTo.AssetMoveID INNER JOIN
                         dbo.Stores ON dbo.AssetHistory.StoreID = dbo.Stores.StoreId AND dbo.Customers.CustomerID = dbo.Stores.CustomerID INNER JOIN
                         dbo.TerminalName ON dbo.AssetHistory.TerminalID = dbo.TerminalName.TerminalID AND dbo.Customers.CustomerID = dbo.TerminalName.CustomerID

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JDay2

ASKER

I understand the answer but when I run the above I get

MSG 4108, Level 15, State 1, Line 12

Windowed functions can only appear in the select or order by clauses

If I take out the where I get everything and I only need the idx=1 for each asset.

JDay2

ASKER

I got the answer by doing this.

WITH assetrecord AS
(
SELECT        a.AssetHistoryID, a.AssetID, 
  row_number() over (partition by b.Asset order by b.Asset, a.AssetHistoryID desc) idx,
  c.CustomerName, g.PartNumber, b.Asset, a.CreatedOn, d.MoveTitle, e.StoreNumber, a.CreatedBy, f.TerminalName, a.Reference, 
                         a.Tracking, a.Carrier, a.Notes
FROM            dbo.AssetHistory AS a INNER JOIN
                         dbo.Asset AS b ON a.AssetID = b.AssetID INNER JOIN
                         dbo.Customers AS c ON a.CustomerID = c.CustomerID AND b.CustomerID = c.CustomerID INNER JOIN
                         dbo.AssetMoveTo AS d ON a.AssetMoveID = d.AssetMoveID INNER JOIN
                         dbo.Stores AS e ON a.StoreID = e.StoreId AND c.CustomerID = e.CustomerID INNER JOIN
                         dbo.TerminalName AS f ON a.TerminalID = f.TerminalID AND c.CustomerID = f.CustomerID INNER JOIN
                         dbo.Frus AS g ON b.FrusID = g.FrusID AND c.CustomerID = g.CustomerID
						 )
 SELECT *
 FROM assetrecord
 WHERE idx=1

Open in new window