Web Development

Question

Gravatar
0 Votes
Locked

Help with sql query

By jamie.malvone ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

This what ye looking for?

by Shellbot In reply to Help with sql query

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??

gravatar
Collapse -

Cheers

by jamie.malvone In reply to This what ye looking for?

Exactly what I was looking for thanks

gravatar
Collapse -

No problem..

by Shellbot In reply to Cheers

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

Back to Web Development Forum

Start or search

Related Discussions

Related Forums