Ramesh said:
Hi Dirk,
Looks like you have used two tables in your query. All 3 fields I
have mentioned comes from the same table. Though I do have another
table with the details of each Job ID, but I didnt mention that in my
problem.
Can you help with the single table?
No, Ramesh, the query only uses one table -- but it uses it twice, each
time with a different alias. That's what the "AS A" and "AS B" clauses
are doing: defining aliases or "alternate names" for the table and for
a subquery of the table. This is probably more complicated SQL than
you're familiar with, but let's take it apart and look at it from the
inside out.
Look at this part:
(SELECT
YourTable.JobID,
Max(YourTable.ActionDate) AS ActionDate
FROM YourTable
GROUP BY YourTable.JobID) AS A
The SELECT statement in parentheses defines a totals query that groups
the data in YourTable by JobID and returns one record for each unique
JobID in the table, containing the JobID and the maximum (i.e., latest)
ActionDate for that JobID. We take that totals query and give it the
alias "A", so that we can treat it as if it were a table itself.
If all we wanted was to get the latest ActionDate for each JobID, we'd
be done already. But unfortunately, we need to get the ActionTaken on
that date. For that we have to go back to the table again, and find the
record that matches the JobID and its maximum ActionDate. So we say:
INNER JOIN
YourTable AS B
This takes that "derived table" A and joins it to ... YourTable again!
To avoid any confusion in referring to this second copy of YourTable, we
give it a different alias; I chose B, for simplicity. So now we have
one set of records (from A) containing the JobID and ActionDate we're
interested in, and another set (from B) containing all the records, and
we match them up:
ON (A.ActionDate = B.ActionDate) AND (A.JobID = B.JobID);
The INNER JOIN discards any records from either A or B that don't find a
match. Of course, all records from A have matches, because they came
from YourTable in the first place, so it's really only the unwanted
records from B that get filtered out by this join. This gives us an
intermediate result set that contains the JobID and latest ActionDate
from A, and all the fields from the record in B that match that JobID
and ActionDate. From that set of records we select out the fields we
want:
SELECT A.JobID, A.ActionDate, B.ActionTaken
FROM
...
And we have what we want.
