I have a query that produces the following result:
mysql> select patient.patient_id_string, survey.survey_date
-> from survey, patient where survey.patient_id = patient.patient_id and survey.survey_score is not null and patient.location_id = 1
-> order by survey.survey_date desc, survey.survey_time desc, patient.patient_id_string;
+-------------------+-------------+
| patient_id_string | survey_date |
+-------------------+-------------+
| 2134 | 2007-07-27 |
| 2134 | 2007-07-27 |
| 8967 | 2007-07-26 |
| 2345 | 2007-07-25 |
| 2134 | 2007-07-25 |
| 5234 | 2007-07-25 |
| 2453 | 2007-07-25 |
| 5243 | 2007-07-25 |
| 3452 | 2007-07-25 |
| seuoth | 2007-07-11 |
| 23454523 | 2007-07-11 |
| 245524 | 2007-07-11 |
+-------------------+-------------+
12 rows in set (0.00 sec)
However, I would actually like to sort it by desc survey date, but if there is a patient_id_string with multiple entries, then those are grouped together. In the above example it would look like:
+-------------------+-------------+
| patient_id_string | survey_date |
+-------------------+-------------+
| 2134 | 2007-07-27 |
| 2134 | 2007-07-27 |
| 2134 | 2007-07-25 |
| 8967 | 2007-07-26 |
| 2345 | 2007-07-25 |
| 5234 | 2007-07-25 |
| 2453 | 2007-07-25 |
| 5243 | 2007-07-25 |
| 3452 | 2007-07-25 |
| seuoth | 2007-07-11 |
| 23454523 | 2007-07-11 |
| 245524 | 2007-07-11 |
+-------------------+-------------+
(the third 2134 patient id was grouped with the other two)
I just can't figure this out. Any ideas? I know I should be able to do it in the database rather than in my own code.
mysql> select patient.patient_id_string, survey.survey_date
-> from survey, patient where survey.patient_id = patient.patient_id and survey.survey_score is not null and patient.location_id = 1
-> order by survey.survey_date desc, survey.survey_time desc, patient.patient_id_string;
+-------------------+-------------+
| patient_id_string | survey_date |
+-------------------+-------------+
| 2134 | 2007-07-27 |
| 2134 | 2007-07-27 |
| 8967 | 2007-07-26 |
| 2345 | 2007-07-25 |
| 2134 | 2007-07-25 |
| 5234 | 2007-07-25 |
| 2453 | 2007-07-25 |
| 5243 | 2007-07-25 |
| 3452 | 2007-07-25 |
| seuoth | 2007-07-11 |
| 23454523 | 2007-07-11 |
| 245524 | 2007-07-11 |
+-------------------+-------------+
12 rows in set (0.00 sec)
However, I would actually like to sort it by desc survey date, but if there is a patient_id_string with multiple entries, then those are grouped together. In the above example it would look like:
+-------------------+-------------+
| patient_id_string | survey_date |
+-------------------+-------------+
| 2134 | 2007-07-27 |
| 2134 | 2007-07-27 |
| 2134 | 2007-07-25 |
| 8967 | 2007-07-26 |
| 2345 | 2007-07-25 |
| 5234 | 2007-07-25 |
| 2453 | 2007-07-25 |
| 5243 | 2007-07-25 |
| 3452 | 2007-07-25 |
| seuoth | 2007-07-11 |
| 23454523 | 2007-07-11 |
| 245524 | 2007-07-11 |
+-------------------+-------------+
(the third 2134 patient id was grouped with the other two)
I just can't figure this out. Any ideas? I know I should be able to do it in the database rather than in my own code.