Date: 07/14/09 (MySQL Communtiy)    Keywords: mysql, sql

I have some difficulties in building a right query for a situation. A MySQL Table 'patients' looks like this

Name Doctor Date
-------------------------------
John Deere Dr.Watson 2009-02-11
Bill Fairy Dr.Dylan 2009-03-03
John Deere Dr.Dylan 2009-05-19

I'm trying to count records with same Name field AND all other fields in reply must contain appropriate field in LAST record (based in Date field). I'm doing following query:

SELECT *, COUNT(*) AS num FROM patients GROUP BY name, HAVING COUNT(*)>1 ORDER BY date DESC;

I think that ORDER BY DESC helps me to get in reply fields of last record. But in reply I always receive data contained in my oldest record :( I stumbled :) Anyone can help?

Thanks in advance.

Source: http://mysql.livejournal.com/136661.html