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

JDay2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
You can use row_number syntax with partition to get the sequential number of your records.

ROW_NUMBER (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms186734.aspx

try:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JDay2Author Commented:
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.
0
JDay2Author Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.