Returning most recent records?

Problem: how to have query show only most recent records.

This query shows all exams in 2005 for particular individual (grades not shown
to avoid embarrassing John Slacker!):

SELECT examhistory.coursekey, students.userid, students.firstname,
students.lastname,
examhistory.examdate,coursekeylookup.examdescription
FROM students, examhistory, coursekeylookup
AND students.userid='1234567'
AND examhistory.examdate>{d '2004-12-31'}
AND examhistory.coursekey=coursekeylookup.coursekey;

| coursekey | userid | firstname | lastname | examdate | examdescription
+-------------+---------+-----------+----------+------------ -+----------------
| 20 | 1234567 | JOHN | SLACKER | 2005-04-15 | Basket Weaving
| 25 | 1234567 | JOHN | SLACKER | 2005-04-15 | Remedial English
| 22 | 1234567 | JOHN | SLACKER | 2005-08-18 | Popular Culture
| 22 | 1234567 | JOHN | SLACKER | 2005-09-20 | Popular Culture
| 22 | 1234567 | JOHN | SLACKER | 2005-10-14 | Popular Culture
| 21 | 1234567 | JOHN | SLACKER | 2005-11-21 | Poetry
| 22 | 1234567 | JOHN | SLACKER | 2005-12-02 | Popular Culture

I would like the query to show the most recent exams for each course like this:

| coursekey | userid | firstname | lastname | examdate | examdescription
+-------------+---------+-----------+----------+------------ -+----------------
| 21 | 1234567 | JOHN | SLACKER | 2005-11-21 | Poetry
| 22 | 1234567 | JOHN | SLACKER | 2005-12-02 | Popular Culture
| 25 | 1234567 | JOHN | SLACKER | 2005-04-15 | Remedial English
| 20 | 1234567 | JOHN | SLACKER | 2005-04-15 | Basket Weaving


Now the example query is for a specific userid, and if I included "select
max(examdate)" and "group by coursekey" then the above results are returned.
However, I want most recent exams for ALL students.

What is the easiest and/or most efficient way to do this query?

Thanks!
--
manny@don't spam on me

Re: Returning most recent records?

"manny" wrote in message
news:khdls1t6p943sjh5h1i171ubkea1o17i09@4ax.com...
> This query shows all exams in 2005 for particular individual (grades not
> shown
> to avoid embarrassing John Slacker!):
....
> However, I want most recent exams for ALL students.

I tested this and it seems to work, returning a result set as you describe.

SELECT h.coursekey, s.userid, s.firstname, s.lastname, h.examdate,
c.examdescription
FROM students AS s
INNER JOIN examhistory AS h ON s.userid = h.userid
INNER JOIN coursekeylookup AS c ON h.coursekey = c.coursekey
LEFT OUTER JOIN examhistory AS h2 ON s.userid = h2.userid AND h.coursekey
= h2.coursekey AND h.examdate < h2.examdate
WHERE h2.examdate IS NULL
AND h.examdate>{d '2004-12-31'}

Regards,
Bill K.

Re: Returning most recent records?

"Bill Karwin" wrote:

>I tested this and it seems to work, returning a result set as you describe.
>
>SELECT h.coursekey, s.userid, s.firstname, s.lastname, h.examdate,
>c.examdescription
>FROM students AS s
> INNER JOIN examhistory AS h ON s.userid = h.userid
> INNER JOIN coursekeylookup AS c ON h.coursekey = c.coursekey
> LEFT OUTER JOIN examhistory AS h2 ON s.userid = h2.userid AND h.coursekey
>= h2.coursekey AND h.examdate < h2.examdate
>WHERE h2.examdate IS NULL
> AND h.examdate>{d '2004-12-31'}
>

Great Bill, that works perfectly. I must admit that I don't fully understand how
this is being built!

Can I simplify the query by using a temporary table created from an initial
query that looks like this (but more users and courses):

coursekey | userid | examdate
----------+---------+-----------
22 | 1234567 | 2005-08-18
22 | 1234567 | 2005-09-20
22 | 1234567 | 2005-10-14
22 | 1234567 | 2005-12-02

and I want to return:

coursekey | userid | examdate
----------+---------+-----------
22 | 1234567 | 2005-12-02

Manny
--
manny@don't spam on me

Re: Returning most recent records?

"manny" wrote in message
news:rljos1l7ek03o9b7ah6nduaqk5j5ci1qmc@4ax.com...
> Great Bill, that works perfectly. I must admit that I don't fully
> understand how
> this is being built!

The original problem is to find the greatest examdate for each
student/course pair.

This can also be stated as finding the examdate for which there is no
examdate on another row that is greater.

SQL joins are good at finding matches. But you can also use an OUTER JOIN
to find where there is _no_ match. If you get a row in the result set from
a left outer join, and the fields from the other side of the join are all
NULL, then you know there was no match.

So the trick I used was to try to match h.examdate < h2.examdate (that is a
self-join). If the match fails, then the row h must have the greatest
examdate value. We know the match failed if h2.examdate is NULL.

Regards,
Bill K.