How do I order by this select?

[comrade1]

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.

 

[r937]

i don't think what you want is possible :-(

tip: next time, please post in forum436

bonus tip: if you have separate date and time columns, consider combining them into one datetime column



r937.com | rudy.ca

 

[IGNIETO]

Hi

I think it’s possible but you have made a composed query like that:

Select a.patient_id_string, a.survey_date from

(select patient.patient_id_string patient_id_string, survey.survey_date survey_date
from
survey,
patient
where survey.patient_id = patient.patient_id
and survey.survey_score is not null
and patient.location_id = 1 ) a

(select patient.patient_id_string patient_id_string, min(survey.survey_date ) survey_date
from
survey,
patient
where survey.patient_id = patient.patient_id
and survey.survey_score is not null
and patient.location_id = 1) b

where a. patient_id_string=b. patient_id_string

order by b.survey_date desc,
a.survey_time desc,
a.patient_id_string


I can’t probe this query but it’s the form to do that you like, it’s possible that you have to do any change of the query.

Bye