Jester's place (lsergei) wrote in mysql,
Jester's place

Searching and counting

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.
  • Post a new comment


    default userpic
    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.