matthew330

I'm trying to write a query where i have multiple subjects with multiple visits. I need to pull the most recent visit from each subject (i.e. - limit the date field to "most recent"). Any help is appreciated. I have already wasted too much time on this.

Rawb

I wish I could help you further, but since I'm new to the featureful SQL world (I have been using MySQL for too long) I don't know the specifics of how to do this. But here is my shot at it anyways:

SELECT * FROM person
LEFT JOIN
( SELECT personid,MAX(visittime) FROM visit GROUP BY personid )
AS lastvisit ON ( person.personid = lastvisit.personid );

Should get you somewhere close.

If you are stuck in a DB engine that doesn't support subselects, you could either select into a temporary table and then select out of that (the MySQL way), or you could just process the list directly and figure it out for yourself.

Another possible option, if this is a frequent query, is to un-normalize that data, and store the last visit time (or id, depending on what you want to do) in the subject record.

asshopo

Rawb is right on both suggestions. If your database has over, say, 100,000 records, you will want to de-normalize the data. Otherwise, you have to perform that query, which has a subselect containing a group by, then joining and comparing the joined data each time that query is hit. Assuming it's a frequently hit page, it will cause high load on your database server.

__________________
Patterns have a habit of repeating themselves.

quannum

Yes, but assuming your columns are correctly indexed, the overhead shouldnt be massive. You should probably create a compound index across the ID you wish to join with and the date column. Optimising is really platform specific so mebbe you could give us some pointers as to what you are using...

Denormalising data is a good way to go as long as you can maintain data integrity.