Select column of the Max(value)

Question

I have these tables

Records:

+----+-------------+--------------+-------------------------+---------+
| Id | IdHealtcare | ValueMonitor |       DateMonitor       | Checked |
+----+-------------+--------------+-------------------------+---------+
|  1 |           1 |           80 | 2020-05-14 19:40:11.873 | NULL    |
|  2 |           2 |           66 | 2020-05-14 20:56:33.360 | NULL    |
|  3 |           1 |          100 | 2020-05-14 20:32:24.397 | NULL    |
|  4 |           3 |           87 | 2020-05-14 20:57:12.390 | NULL    |
+----+-------------+--------------+-------------------------+---------+

Healthcare:

+----+-----------+----------+--------+------------+
| Id | IdPatient | IdDoctor | IdType | RangeValue |
+----+-----------+----------+--------+------------+
|  1 |         1 |        1 |      1 | 0-180      |
|  2 |         1 |        1 |      2 | 50-200     |
|  3 |         2 |        2 |      1 | 90-180     |
+----+-----------+----------+--------+------------+

I want to select for each type of patient's healtcare the Id of the patient and the most recent date with it value.

I have already tried with these query:

select p.CodiceFiscale, p.Surname, p.Name, t.Name, Max(r.DateMonitor), 
   r.ValueMonitor
from dbo.Patients as p
inner join dbo.HealtcareMonitoring as m on m.IdPatient = p.Id 
inner join dbo.Records as r on r.IdHealtcare = m.Id 
inner join dbo.MonitoringTypes as t on t.Id = m.IdType
where m.IdDoctor = 1
group by t.Name, p.CodiceFiscale, p.Surname, p.Name

SELECT m.IdPatient, m.IdType, MAX(r.DateMonitor), r.ValueMonitor AS LastDate
FROM  dbo.Records as r
inner join dbo.HealtcareMonitoring as m on m.Id = r.IdHealtcare  
where m.IdDoctor = 1
GROUP BY m.IdPatient, m.IdType

But I can't select the value because it is not contained in either an aggregate function or the "group by" clause.

How can I fix? Are there a better ways to solve the problem?

Why aren't you adding all columns from select(except the one with agg function) in `group by`? — django-unchained, May 14 '20 at 16:32
I do not understand what you mean by "for each type of patient's healtcare". That perhaps comes from the table `MonitoringTypes`? We need to see sample data from that and `Patients` as well. — avery_larry, May 14 '20 at 16:37
Why do you always leave the column `r.ValueMonitor` out of your `GROUP BY`??? If you don't want to put in `GROUP BY`, then remove it from your `SELECT`. — Eric, May 14 '20 at 17:39
In other words I want to select the last value and date for each MonitoringType of a patients (and his FiscalCode/Surname-Name) — cese01, May 14 '20 at 21:14

score 0 · Answer 1 · answered May 14 '20 at 16:33

0

Why aren't you adding r.ValueMonitor in group by? It should fix that error

SELECT m.IdPatient, m.IdType, MAX(r.DateMonitor), r.ValueMonitor AS LastDate
    FROM  dbo.Records as r inner join dbo.HealtcareMonitoring as m
    on m.Id = r.IdHealtcare  
    where m.IdDoctor = 1
    GROUP BY m.IdPatient, m.IdType, r.ValueMonitor

answered May 14 '20 at 16:33

django-unchained

844
9
21

Doesnt work, it takes 2 values for the same monitor – cese01 May 14 '20 at 21:13

rlm96 · Answer 2 · 2020-05-14T21:29:55.980

One solution could be group by non-aggregated columns

select  p.CodiceFiscale, p.Surname, p.Name, t.Name, Max(r.DateMonitor), r.ValueMonitor from dbo.Patients as p
    inner join dbo.HealtcareMonitoring as m
    on m.IdPatient = p.Id 
    inner join dbo.Records as r
    on r.IdHealtcare = m.Id 
    inner join dbo.MonitoringTypes as t
    on t.Id = m.IdType
    where m.IdDoctor = 1 group by t.Name, p.CodiceFiscale, p.Surname, p.Name
group by p.CodiceFiscale, p.Surname, p.Name, t.Name, r.ValueMonitor

Another solution could be use a subquery, but the permormance is worse

select  p.CodiceFiscale, p.Surname, p.Name, t.Name, 
(select MAX(DateMonitor) from dbo.Records where IdHealtcare = m.Id)
, r.ValueMonitor from dbo.Patients as p
    inner join dbo.HealtcareMonitoring as m
    on m.IdPatient = p.Id 
    inner join dbo.MonitoringTypes as t
    on t.Id = m.IdType
    where m.IdDoctor = 1 group by t.Name, p.CodiceFiscale, p.Surname, p.Name

More about join vs subqueries performance here SQL Joins Vs SQL Subqueries (Performance)?

Sorry, actually the second one doesn't have group by clause, copy&paste problems :) — rlm96, May 14 '20 at 21:30

Select column of the Max(value)

2 Answers2