expert question - combining tables and MAX()

I have two tables - T_Assets and T_Inspections.

I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:

SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)

FROM T_Assets

INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID

GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID

Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.

Can anyone please tell me a way around this.

Jagdip

Your on the right track, but I think what you need to do instead is use the TOP and the ORDER BY if you only want the latest inspection. Or you could use the UNION Command

I recently did something very similar in a stored procedure, which makes it much easier, when you start accounting for null row's and such.

Here's a sample SQL Mockup using the TOP command, if your interested in the stored procedure method, let me know, I'll post a mockup of it here was well.

If you really want to get down and dirty, you could use Datashaping, which is pretty powerfull

http://support.microsoft.com/kb/q189657/

|||

Sorry, I dont quite follow you. Let me give you what I have so far:

I have the following simplified tables

T_Assets (I_AssetID)

T_Inspections (I_AssetID, I_InspectionID, SDT_InspectionDate)

Now, when I use the following sql:

SELECT I_AssetID, I_InspectionID, MAX(SDT_InspectionDate)

FROM T_Inspections

INNER JOIN T_Assets ON T_Assets.I_AssetID = T_Inspections.I_InspectionID

I get the wrong results, i.e. I get all the inspections instead of just the newest ones. This is because the I_InspectionID is in the same table as the column I am using the aggregate function on. If I remove the I_InspectionID from the query, then I get the results I want (except I need the I_InspectionID).

|||

Which is why you need to use the TOP argument, so that you only return one row (the most recent inspection in this case) Using SELECT TOP 1 will select just the top record in the database for the given parameters.

SELECT TOP 1 from T_Inspections, INNER JOIN ON T_Inspections.InspectionID = T_Assets.I_AssetID

Try that, let me know where it hangs up at, or doesnt give you the right information, and we can resolve it a little bit.

|||

Oh, I see what you mean. But I dont want the last inspection for one asset, I want the last inspection forall assets.

Using the TOP 1 will only get me the asset which had the last inspection.

|||

Yep, your right. Ok, lets try again.

I'm not sure how to do this in a standard SELECT Statment, this is where I'd typicall move to a stored procedure, but lets give it one more shot, and then possibly explore the stored procedure aspect.

You have multiple rows in Table A, with a PK, you have multiple rows in Table B that contains a FK, you need All rows from Table A, and the top row from table B where it coresponds to Table A's PK? Sounds right.

|||

K, I've tested some things out, and think you can this done with a subquery. Here's how I'd do it.

Table A = Your Assets Table

Table B = your maintenance Table

Select * From tableA (Select TOP 1 tableb.field1 from tableb where tablea.pk = tableb.fk) as tablefind1 from tableA

|||

You didn't specify if you wanted the assets that have no inspections done yet, so I'll assume you do:

SELECT t1.AssetID,t1.InspectionID,t2.LastInspection

FROM Assets t1

LEFT JOIN (

SELECT AssetID,MAX(SDT_InspectionDate)

FROM Inspections

GROUP BY AssetID) t2 ON t1.AssetID=t2.AssetID

That should work, just add your silly T_ and I_ prefixes where appropriate.

|||Thanks for both your help. I did need a sub-query to accomplish this, but it was not easy. It is slightly different from what both of you gave, but that is only because I gave you a simplified version of the schema. If you would like to see my answer, then just ask.