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. I would like to get the latest row for each medication in my table. (The latest row is determined by the max function, getting the latest date).

How do I fix this problem?

the one that is used by visual studio 2008 coding in c#. I think it's sql server right? — jello, Feb 28 '10 at 00:21
See also: http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column, http://stackoverflow.com/questions/95866/select-max-in-group, http://stackoverflow.com/questions/612231/sql-select-rows-with-maxcolumn-value-distinct-by-another-column, and many others. — outis, Feb 28 '10 at 04:44

score 5 · Accepted Answer · answered Feb 28 '10 at 00:30

5

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 = ?

answered Feb 28 '10 at 00:30

OMG Ponies

325,700
82
523
502

+1 - just pipped me to the post. This is the correct (and medically safe!) answer as it will give the latest dosage, not the highest dosage that MAX() would give (which would not necessarily be the latest dose). My only addition would be, if there are multiple records with the same date - if that's the case, you'd need to use another field in addition to determine the latest (e.g. an IDENTITY field). – AdaTheDev Feb 28 '10 at 00:35
thx a lot man (even tho i have a little bit of trouble understanding your statement... no worries i'll figure it out). sorry for the late reply – jello Mar 01 '10 at 23:46
hmmmm weird this doesn't seem to work.... it only gives me 2 rows, while it's supposed to give 3... – jello Mar 03 '10 at 04:18
@jello: What does the data look like? – OMG Ponies Mar 03 '10 at 04:36
@OMG Ponies: what do you mean exactly? – jello Mar 04 '10 at 00:24
@jello: Post some example data, so I can try to reproduce the issue – OMG Ponies Mar 04 '10 at 01:33

score 1 · Answer 2 · edited May 23 '17 at 11:47

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?

score 0 · Answer 3 · edited Jun 11 '11 at 13:07

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.)

Group by SQL statement

3 Answers3