Web Development

Ask something
Question Locked

Help with sql query

0 helpful
4 replies

by jamie.malvone

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi , I have two tables as shown below
Faults table
FaultID Status
1 Cleared
2 In Progress
3 In Progress

Actions table
FaultID ActionNo
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3

I cant change the tables (there are alot more fields in each of them but this is all I need for my question)

What i'm attempting to get is the last ActionNo for each FaultId for example
Query
faultid actionno status
1 3 Cleared
2 2 In Progress
3 3 In Progress

My query so far

SELECT Faults.Faultid, Faults.Status, ACTIONS.actionnumber
FROM Faults INNER JOIN ACTIONS ON Faults.[Faultid] = ACTIONS.[Faultid];

Any help would be greatly appreciated

4 replies

by shellbot

select f.faultid, f.status, max(a.actionnumber)
from faults f
inner join actions a on f.faultid = a.faultid
group by f.faultid, f.status

should give you
FaultID Fault Status ActionNumber
1 Cleared 3
2 In Prog 2
3 In Prog 3

Or am i way off base here??

by shellbot

I'd be especially greatful if you gave me a thumbs up though :)

This discussion is closed to new replies.