468,783 Members | 1,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,783 developers. It's quick & easy.

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.examdescripti on
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
Jan 15 '06 #1
3 1703
"manny" <ma***@dontspamonme.net> wrote in message
news:kh********************************@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.
Jan 16 '06 #2
"Bill Karwin" <bi**@karwin.com> 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
Jan 17 '06 #3
"manny" <ma***@dontspamonme.net> wrote in message
news:rl********************************@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.
Jan 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Nova's Taylor | last post: by
2 posts views Thread by news | last post: by
2 posts views Thread by Neil Aggarwal | last post: by
13 posts views Thread by Kirk McDonald | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.