Need SQL help to get lastest record based on date of entry

[mabus]

I need some SQL help. Here's my table:

 

Activity

- Id (PK)

- ProfileId (FK)

- Date

 

Each record represents a profile's activity in the CMS we're building (e.g. user updated profile, user uploaded photo, etc.) I'm only interested Id, ProfileId, and Date of the latest activity. How do I get that?

 

[richardw]

hi,

 

try using

[MAX(date) as last_record

 

however, you indicated date of entry, and also reffered to last modification. if that is the case, make sure to build in a routine To upate your date field on record modification.  Keep in mind, if it is date of entry and it you have an autoincrement Id field you can probably select on The last id.

 

best

[richardw]

there is also a good solution posted a day or two ago

 

http://www.phpfreaks.com/forums/index.php/topic,214197.0.html

[mabus]

THanks for the reply guys. However, doing a MAX on the dates did not quite work for me. The other approach of sorting by date in descending order, and limiting the result to 1, resulted to creating a 'SELECT' statement within an outer 'SELECT' statement. For example...

 

SELECT tableOne.SomeId, (select tableOne.SomeId, tableone.Date from TableOne order by date DESC limit 1 ) FORM tableOne as tableOne WHERE ...<snip>

 

It was something like this. I got the result that I want, but am dissapointed 'cause the execution time reached about 12-13 seconds. This is not good, in this case. Any suggestions on how I may optimize the query?

[fenway]

I'm confused -- you want just a single row? the most recent one for each user? Please clarify.

[mabus]

I want each user to have one row, and the dates that is to be displayed is the most recent one.

 

 

[fenway]

I want each user to have one row, and the dates that is to be displayed is the most recent one.

There must be other fields, though... you'll need profileID (which I assume is the "user") and date just to get the most recent, which leaves only the surrogate PK?