Avatar of Talon69
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQL query problem - getting latest results per asset

Running an SQL Asset Tracking database. We have a table with all of our assets listed and a table that contains the Portable appliance data. They are linked via a third table that contains details of each task.

What I want to generate is a report that shows the latest test data for each asset.

For example, the 3 tables are called Asset, PAT and PPM. One asset can have many PAT records linked via a foreign key in the PAT table to the primary key of the PPM table. The PPM table has a foreign key to the primary key of the Asset table.
I would like to select the asset ID from the asset table and the date last tested from the PAT table.

Could some SQL guru please offer some guidance???

Many thanks in advance!

Microsoft SQL ServerDB Reporting ToolsSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

Without knowing the structure and sample data it is difficult to provide any guidance.
But normally when we call "latest", it is either based on some date/time field or based on some incremented number generated.

Please provide us the Structure of 3 tables and some sample records.

The query that you want should not be difficult one.


Thanks for the reply.

Sample data:

Asset table :
Ass_Pri    AssetID   Asset_desc
001         12345A   Monitor
002         12345B   PC
003         12345C   Printer

PAT table:
PAT_pri      TestDate    Pass/Fail     PPM_pri_seq
101            01/01/10         P           5000
102            01/01/10         P           5001
103            01/01/10         P           5002
104            01/01/11         P           5003
105            01/01/11         P           5004
106            01/01/11         P           5005
107            01/11/12         P           5006
108            01/11/12         P           5007
109            01/11/12         P           5008

PPM table:
PPM_pri    PPM_desc     Ass_pri_seq
5000         PAT test        001
5001         PAT test        002
5002         PAT test        003
5003         PAT test        001
5004         PAT test        002
5005         PAT test        003
5006         PAT test        001
5007         PAT test        002
5008         PAT test        003

This is the very basic data. When I mean latest, it relates to both the latest in date i.e. closest to now and also it could mean the highest PAT_pri (PAT table primary key) per asset.

Hope this helps

Forgot to add:

The data I would hope to extract would be:

AssetID     TestDate    Pass/Fail
12345A     01/11/12        P
12345B     01/11/12        P
12345C     01/11/12        P

And not to see previous results.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Thank you both very much!

@rajeevnandanmishra: Good answer, thanks!

@keyu: Only slight problem I had was that SQL didn't like having an aggregate function in the ON clause but you still gave me the right ideas. Good answer though, thank you!