--- Russell Horn <[EMAIL PROTECTED]> wrote:
> Hi, > > I'm storing data against a bunch of people and want to track how it > changes. So, I have a person table where everyone has a person ID and > a > results table a bit like this: > > | personID | classification | date | > | 1 | 0 | 2005-11-10 | > | 2 | 3 | 2005-11-10 | > | 3 | 1 | 2005-11-10 | > | 4 | 0 | 2005-11-10 | > | 1 | 3 | 2005-12-01 | > | 4 | 2 | 2005-12-03 | > | 1 | 2 | 2005-12-23 | > | 5 | 1 | 2006-01-03 | > | 2 | 2 | 2006-12-03 | > > This lets me see how things change as a pattern, for example > comparing a > a > > SELECT classification WHERE DATE < '2006-01-01' GROUP BY > classification > > and comparing it to: > > SELECT classification WHERE DATE >= '2006-01-01' AND DATE <= > '2006-01-31' GROUP BY classification > > But is there a way I can select every personID's most recently > expressed > preference? > > I hope this makes sense - in the table above, person 1 had a > classification of 0 at 10th November, but this changed to 2 on 23rd > Decembner. Can I write a query to select personID once together with > their latest preference, or indeed their preference as expressed at a > specific point in time? > > Thanks as ever for any suggestions. > > Russell. > Yet another variety of the FAQ - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html In this case, what you are trying to maximize is the date per person_id. It should be fairly easy for you to convert the examples to match your situation. Shawn Green Database Administrator Unimin Corporation - Spruce Pine __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
