group by sql statement

Question

so i got this statement, which works fine:

SELECT   MAX(patient_history_date_bio) AS med_date, medication_name
FROM     biological
WHERE    patient_id = 12)
GROUP BY medication_name

but, I would like to have the corresponding medication_dose also. So I type this up

SELECT   MAX(patient_history_date_bio) AS med_date, medication_name, medication_dose
FROM     biological
WHERE    (patient_id = 12)
GROUP BY medication_name

But, it gives me an error saying:

"coumn 'biological.medication_dose' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.".

So I try adding medication_dose to the GROUP BY clause, but then it gives me extra rows that i don't want. What I would like to do is get the latest row for each medication in my table. (latest row is determined by the max function, getting the latest date).

Any ideas?

Answer 1

you need to put max(medication_dose) in your select. group by returns a result set that contains distinct values for fields in your group by clause, so apparently you have multiple records that have the same medication_name, but different doses, so you are getting two results. By putting in max(medication_dose) it will return the maximum dose value for each medication_name. You can use any aggregate function on dose (max, min, avg, sum, etc.)

Answer 2

If you really have to, as one quick workaround, you can apply an aggregate function to your medication_dose such as MAX(medication_dose).

However note that this is normally an indication that you are either building the query incorrectly, or that you need to refactor/normalize your database schema. In your case, it looks like you are tackling the query incorrectly. The correct approach should the one suggested by OMG Poinies in another answer.

You may be interested in checking out the following interesting article which describes the reasons behind this error:

• But WHY Must That Column Be Contained in an Aggregate Function or the GROUP BY clause?

Answer 3

Use:

SELECT b.medication_name,
       b.patient_history_date_bio AS med_date,
       b.medication_dose
  FROM BIOLOGICAL b
  JOIN (SELECT y.medication_name,
               MAX(y.patient_history_date_bio) AS max_date
          FROM BIOLOGICAL y
      GROUP BY y.medication_name) x ON x.medication_name = b.medication_name
                                   AND x.max_date = b.patient_history_date_bio
 WHERE b.patient_id = ?