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!

W

Rajeev

Hi,
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.

Talon69

ASKER

Hi,

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

Talon69

ASKER

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.

HTH

ASKER CERTIFIED SOLUTION

Rajeev

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

keyu

membership

This solution is only available to members.

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

Start Free Trial

Talon69

ASKER

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!