Choosing values from multiple columns based on latest value of one column

Question

I have an Azure Cosmos DB that contains columns DateTime, userName, temperature, and bloodPressure, plus many other columns. There are several distinct userName values, and each userName has temperature and bloodPressure data collected at different values of DateTime. The temperature and bloodPressure values are different for each of those 15 times.

Here is an example of the data,

userName, DateTime, bloodPressure, temperature, heartBeat, hatSize
Curly, "2021-01-19 18:24:53", 121, 98, 60, 7.25
Larry, "2021-01-14 18:24:53", 125, 99, 80, 7.5
Mo, "2021-01-13 18:24:53", 123, 100, 70, 7.75
Curly, "2021-01-18 18:24:53", 120, 97, 50, 8
Larry, "2021-01-13 18:24:53", 119, 99, 75, 7.15
Larry, "2021-01-15 18:24:53", 115, 98, 85, 7.37
Mo, "2021-01-12 18:24:53", 110, 102, 100, 8.01
Mo, "2021-01-20 18:24:53", 130, 99, 110, 6.97
Larry, "2021-01-18 18:24:53", 127, 98, 72, 7.76
Curly, "2021-01-17 18:24:53", 126, 97, 82, 8.0

For each user, I would like to return the DateTime of their latest measurement, and their temperature and bloodPressure from their latest DateTime:

Larry, "2021-01-18 18:24:53", 127, 98
Mo, "2021-01-20 18:24:53", 130, 99
Curly, "2021-01-19 18:24:53", 121, 98

I tried this,

SELECT  
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure, 
    c.temperature
FROM CheckupData c
GROUP BY 
    c.userName

but got an error that GROUP BY should include the other outputs of SELECT. Since bloodPressure and temperature are always changing, it seems like I shouldn't use them in GROUP BY, because I only want the lastest values. What am I doing wrong? (If it matters, my real database is larger: 50,000 userNames measured at 50 or so DateTime values each, and 30 or so columns).

Jonathan Fite · Answer 1 · 2021-04-09T13:48:56.780

I don't have an environment to test with, but the below might work for you.

EDIT: I see now that this is the same approach as @Verace's comment, but still, it should work in Cosmos DB.

https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery

SELECT c.userName
    , c.DateTime 
    , c.bloodPressure
    , c.temperature
FROM CheckupData c
    JOIN  (SELECT g.userName, MAX(g.DateTime) AS MaxDateTime FROM CheckupData g GROUP BY g.userName) m
WHERE m.userName = c.userName 
    AND m.MaxDateTime = c.DateTime

nbk · Answer 2 · 2021-04-09T17:22:09.197

EVERY Column in a SELECT has to be in the GROUP BY or use a aggregator function

SELECT  
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure, 
    c.temperature
FROM CheckupData c
GROUP BY 
    c.userName,c.bloodPressure, c.temperature

OR

SELECT  
    c.userName,
    MAX(c.DateTime),
    AVG(c.bloodPressure), 
    AVG(c.temperature)
FROM CheckupData c
GROUP BY 
    c.userName

As we don't know nothing about your data and desired result. You have to choose what values you need.

For big tables you can use

CREATE TABLE CheckupData 
    ([userName] varchar(5), [DateTime] varchar(21), [bloodPressure] int, [temperature] int, [heartBeat] int, [hatSize] int)
;
INSERT INTO CheckupData 
    ([userName], [DateTime], [bloodPressure], [temperature], [heartBeat], [hatSize])
VALUES
    ('Curly', '"2021-01-19 18:24:53"', 121, 98, 60, 7.25),
    ('Larry', '"2021-01-14 18:24:53"', 125, 99, 80, 7.5),
    ('Mo', '"2021-01-13 18:24:53"', 123, 100, 70, 7.75),
    ('Curly', '"2021-01-18 18:24:53"', 120, 97, 50, 8),
    ('Larry', '"2021-01-13 18:24:53"', 119, 99, 75, 7.15),
    ('Larry', '"2021-01-15 18:24:53"', 115, 98, 85, 7.37),
    ('Mo', '"2021-01-12 18:24:53"', 110, 102, 100, 8.01),
    ('Mo', '"2021-01-20 18:24:53"', 130, 99, 110, 6.97),
    ('Larry', '"2021-01-18 18:24:53"', 127, 98, 72, 7.76),
    ('Curly', '"2021-01-17 18:24:53"', 126, 97, 82, 8.0)
;

GO

SELECT
cd.[userName], cd.[DateTime], cd.[bloodPressure], cd.[temperature], cd.[heartBeat], cd.[hatSize]
FROM CheckupData cd INNER JOIN 
(SELECT MAX([DateTime]) lastdate,[userName] FROM CheckupData GROUP BY [userName]) cd1
ON cd.[userName]  = cd1.[userName] AND cd.[DateTime] = cd1.lastdate
GO

userName | DateTime              | bloodPressure | temperature | heartBeat | hatSize
:------- | :-------------------- | ------------: | ----------: | --------: | ------:
Mo       | "2021-01-20 18:24:53" |           130 |          99 |       110 |       6
Larry    | "2021-01-18 18:24:53" |           127 |          98 |        72 |       7
Curly    | "2021-01-19 18:24:53" |           121 |          98 |        60 |       7

db<>fiddle here

Choosing values from multiple columns based on latest value of one column

2 Answers2