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

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

    Error

    default userpic
  • 3 comments
um... may be this:
SELECT p1.*, p2.total FROM patients p1 INNER JOIN
(SELECT name, count(*) total, max(date) max_date FROM patients GROUP BY name) p2
ON (p1.name=p2.name AND p1.date=p2.max_date)
Well... Seems too complicated :)

What you mean as p1 and p2?
p1 and p2 are aliases, to distinguish between table and sub-query

the easiest (and the most efficient) way is
1) get list using your query:
SELECT name, COUNT(*) AS num FROM patients GROUP BY name, HAVING total>1
2) loop thru it:
SELECT * FROM patients WHERE name=$name ORDER BY date DESC LIMIT 1