do a select patient_id,treatment_date from patient_treatment_history order by treatment_date desc limit 1; I am not sure but max(treatmen_date) might work too as such: select patient_id, max(treatment_date) from patient_treatment_history group by treatment_date; In your original query the group by clause is causing undesired results.
----- Original Message ----- From: "lorenzo.kh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 10, 2002 6:53 PM Subject: sql query(group by + order by) > Hi, > I got a table named patient_treatment_history > Below is some of the records inside the table. > > patient_id,treatment_date,charges > 1014,2002-01-28,20 > 1001,2002-02-02,100 > 1026,2002-04-08,74 > 1001,2002-04-15,85 > 1014,2002-05-05,50 > 1030,2002-05-16,125 > 1030,2002-06-18,180 > 1001,2002-06-25,125 > > > I try to retrieve the last visit date for each patient using this query: > select patient_id,treatment_date from patient_treatment_history group > by(patient_id) order by treatment_date desc. > But the result is not what i expected. > Can anybody assist me on this? > > Thanks. > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php