Tilted Forum Project Discussion Community  

Go Back   Tilted Forum Project Discussion Community > Interests > Tilted Technology


 
 
LinkBack Thread Tools
Old 01-10-2005, 11:18 AM   #1 (permalink)
Banned
 
SQL help

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.
matthew330 is offline  
Old 01-10-2005, 02:40 PM   #2 (permalink)
Crazy
 
Location: Salt Town, UT
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.
Rawb is offline  
Old 01-10-2005, 07:53 PM   #3 (permalink)
Insane
 
Location: Michigan
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.
asshopo is offline  
Old 01-29-2005, 10:45 PM   #4 (permalink)
Upright
 
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.
quannum is offline  
 

Tags
sql

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -8. The time now is 12:47 AM.

Tilted Forum Project

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Search Engine Optimization by vBSEO 3.6.0 PL2
© 2002-2012 Tilted Forum Project

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47