2

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

Improve this question
3
  • 1
    If CosmosDB doesn't have window functions, then you want greatest-n-per-group - if user_name is a PK, then you should be able to SELECT c.user_name, MAX(c.date_time) AS mdt FROM checkup_data c GROUP BY c.user_name and then join that back to checkup_data on the PK and mdt - taking the bp and temp... I did something like this today here - there's links within also. Commented Apr 8, 2021 at 19:42
  • 1
    I threw together this - if no window functions are available... Commented Apr 8, 2021 at 20:09
  • 1
    Thanks Verace. I think JOIN ON is different for Azure Cosmos. This example only has JOIN IN. I will try to adapt your code. Commented Apr 9, 2021 at 15:27

2 Answers 2

Reset to default
1

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
Improve this answer
1
  • When I tried this query in the Cosmos portal it said "We have detected you may be using a subquery. Non-correlated subqueries are not currently supported. Please see Cosmos sub query documentation for further information". When I ran the query, I got the error "Identifier CheckupData could not be resolved." Here is some info on that that I will look at. Commented Apr 9, 2021 at 14:57
0

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

Improve this answer
11
  • I added example data to my question, thanks for pointing out that was needed. Is there an aggregator function instead of AVG that will find the bloodPressure and temperature value that was measured at the MAX(c.DateTime) for each user? Commented Apr 9, 2021 at 12:45
  • @KAE i added a query for your question. Commented Apr 9, 2021 at 17:22
  • Thanks so much. Unfortunately it seems like Cosmos DB doesn't allow INNER JOIN, or the square bracket convention like cd.[userName]. It is really hard to figure out how to write a complicated Cosmos DB query that works since it's using its own flavor of SQL. I may just dump all the data out and do it by brute force. Commented Apr 16, 2021 at 21:25
  • There is a SQL query test tool for Azure Cosmos DB here that seems useful for learning it, so I'll see if that helps. Commented Apr 16, 2021 at 21:37
  • 1
    you can use simokly join the inner is implicit and remove the brackets Commented Apr 16, 2021 at 21:45

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.