How would I select all of the people that are currently in a particular group
Suppose that I have a few tables in a database that look (abbreviated) like this:
TGroups
groupid
TUsers
userid
TGroupAudit
groupid
userid
datetimejoined
You know that a person is in a particular group because it is the last group they joined. When a person joins a group, a new record is created in TGroupAudit to record the event. The old records are kept around for reporting/billing purposes and that's what makes this generally difficult for me. A user can be added to a group and later move to another group, but the previous entry still exists, so you have to check the datetime to find out which one is more recent. I can get a single user's group with:
"SELECT groupid FROM TGroupAudit WHERE datetimejoined = (SELECT MAX(datetimejoined) FROM TGroupAudit WHERE userid=000)"
Substitute the desired user's ID in the subquery for '000'. However, running through every single user in the database (thousands of records) to get their group and save just the ones in the desired group in this manner would kill the database. This query could be run a dozen times a minute by admins pulling up reports for their groups.
Select last record for a subset of users
BenziZbit
Try this:
SELECT sub.userid, tga.groupid
FROM TGroupAudit tga
JOIN
( SELECT userid, MAX(datetimejoined) AS maxdate
FROM TGroupAudit
GROUP BY userid
) AS sub
ON tga.userid = sub.userid
AND tga.datetimejoined = sub.maxdate
For more SQL tips, check out my blog:
JS26