SQL Databases

[Nigel Goodwin]

Perhaps a little off topic?, but I thought someone here might be experienced in SQL?.

I've got a database I wrote a number of years ago in Delphi, for keeping grading records for our Ju Jitsu club. There are two linked tables, one with the students details, the other with their grading results, linked by membership number. The grading results table consists of 'membership number', 'belt passed', 'grade awarded', and 'grading date'.

What I want to do is run a query that will return a list of students names, along with the last belt they passed - this would obviously be the most recent date in the grading table. I can easily select grading dates after a certain date, but how can I select the latest one for each student?.

 

[zevon8]

Hi, this may or may not help, but I had done something similar in MS Access a few years back. I did two things, one, was a hidden table that was a number value assigned to a word, and sorted by this number value. The other was to convert a say 10/15/1998 date to a Julian date via function, then sorted by that value.

I seem to remember the Julian method was nicer all-round. You could also associate the query "inside" a row, pulling the latest from that row, then sorting all the results from all the other rows.

Hope this makes sense or gives you an idea

 

[upand_at_them]

What you want is a subquery that gets the date of the latest belt for each student, which you then join back to get the other details.

This is the query that returns the latest belt for each ID:

SELECT MembershipID, MAX(GradingDate) as GradingDate
FROM GradingResults
GROUP BY MembershipID

You then alias it to join back on the student table and the grading table again (for the belt):

SELECT SD.MembershipID,
SD.LastName,
SD.FirstName,
GR.BeltPassed,
GR.GradingDate
FROM StudentDetails as SD,
GradingResults as GR,
(SELECT MembershipID, MAX(GradingDate) as GradingDate
FROM GradingResults
GROUP BY MembershipID) as TMP
WHERE TMP.MembershipID = GR.MembershipID
AND TMP.GradingDate = GR.GradingDate
and TMP.MembershipID = SD.MembershipID

(I'm used to putting the INNER JOINs in the WHERE clause.)

-Mike

P.S. This only works if a student gets only one belt on a date.

 

[Joel Rainville]

I suck at SQL that gets more complicated than the usual SELECT. I'd simply retrieve all the belts and find the latest one in the main program (Delphi) code. Hopefully some SQL guru/DBA will see this thread, but I suspect this forum doesn't attract much DBAs... :lol:

 

[Joel Rainville]

What you want is a subquery that gets the date of the latest belt for each student, which you then join back to get the other details.

Well, there you have it :lol:

 

[upand_at_them]

Hi, this may or may not help, but I had done something similar in MS Access a few years back.

Zevon,

Just so you know...You can do this in MS Access too. What you do is create a query with totals on, grouping on MembershipID and getting the MAX of the date. Save this query and then you can use it in another query, joining on MembershipID, to get the other data.

(You can copy the SQL I wrote and paste it into the SQL view of a query, changing the joins in the WHERE clause to real INNER JOINs. The query then becomes viewable only in SQL view.)

Mike

 

[Nigel Goodwin]

P.S. This only works if a student gets only one belt on a date.

Thanks, very much, I'll give that a go! - and yes, there's only one belt per date, and gradings are normally 4 months apart (three a year).

 

[zevon8]

....Just so you know...You can do this in MS Access too....
Mike

Nice job Mike, yes that is very similar to what I had done. Often I would let the "click and drag wizard" do the basic work in Access, then I would use the SQL editor to fine tune what I wanted to do. The one I had done was for a collection agency database, and very similar queries were common. It was over 5 years ago and the full syntax had escaped me.

There used to be a SQL developer website that was setup akin to an encyclopaedia you could search for snippets of code examples. There was a message board with it also, it was an amazing site. A great resource when you knew you didn't need to reinvent the wheel :lol:

This site is similar: https://www.orafaq.com

 

[upand_at_them]

Often I would let the "click and drag wizard" do the basic work in Access, then I would use the SQL editor to fine tune what I wanted to do.

I started out with Access years ago, but once you learn SQL it's so much easier just to type everything.

There used to be a SQL developer website that was setup akin to an encyclopaedia you could search for snippets of code examples.

I've used this a few times in the past:
https://www.sqlteam.com/

Mike

 

[Nigel Goodwin]

Thank you both, I'll check them out - I haven't had time to look at it again yet 8)