finding the greatest timestamp in an sql table

[stubarny]

Hi,

 

I have a table of factfiles on my database.

 

In another table in the same database I have a list of factfile updates - including the id number of the factfile and the timestamp of when the update occured. If a factfile has been updated 60 times then there will be 60 records in this table.

 

I now want to create a list of factfiles that have been updated with the most recently updated at the top. So I have the following SQL code:

 

$query ="SELECT article_id FROM article_updates ORDER BY timestamp_of_update ASC";

 

Please could you tell me how to modify this code so that I only return one record for each different value of article_id? And also please could you tell me

how to ignore all updates for a given factfile within 7 days of the first update of a given factfile (to stop notifications of article updates appearing more than once per a week for a given factfile).

 

(so if an article is first updated on Monday and then on the Wednesday, Thursday, Saturday and following Tuesday the update notification should only appear on the first Monday and the following Tuesday).

 

Thanks,

 

Stu

[Pandemikk]

$query ="SELECT article_id FROM article_updates
GROUP BY article_id
ORDER BY timestamp_of_update ASC";

 

Simple group by statement will return all (unique) article_ids with the highest timestamp. For your second question we'll need to see your table information.

[fenway]

If you want the entire row, you can't use GROUP BY -- use a self-join.