Query for latest records of a given set of users

SQL++
,
1

Hello,
I’m having a bit of a time figuring out how to return the latest (newest) records for a given set of users. I’ve googled around and bit and have seen some answers but I have not been able to apply what I’ve seen to achieve the results I am looking for.

I have documents in the form:

{
   "api":"testkey",
   "kind":"record",
   "time":1523403198,
   "uid":"todd",
    ...
}

select max(time) as time, uid from mybucket where api == "testkey" and kind == "record" and uid in [ "todd", "foo", "foo1" ] group by uid order by time desc

[
  {
    "time": 1523403198,
    "uid": "todd"
  },
  {
    "time": 1523325599,
    "uid": "foo1"
  },
  {
    "time": 1523283237,
    "uid": "foo"
  }
]

My struggle is that I am looking to get back the entire document with one query where my current approach requires two separate queries with the second query constructed with values returned from the first query.

Many Thanks,
Todd

2

Try this. You’ll get the both the max time and the document ID associated with it. Important to set the attribute names within max correctly so time gets compared first.

select max({"a":time, "b":META(mybucket).id}) as timex, uid from mybucket where api == “testkey” and kind == “record” and uid in [ “todd”, “foo”, “foo1” ] group by uid order by timex desc

3 
CREATE INDEX ix1 ON mybucket(api, kind, uid, time DESC) ;

SELECT m[1].* FROM mybucket AS t
WHERE t.api == "testkey" AND t.kind == "record" AND t.uid IN [ "todd", "foo", "foo1" ]
GROUP BY t.uid
LETTING m = MAX([t.time,t])
ORDER BY m[0] DESC;

The following gives document key instead of whole document

SELECT t.uid, m[1] AS dockey, m[0] AS time FROM mybucket AS t
WHERE t.api == "testkey" AND t.kind == "record" AND t.uid IN [ "todd", "foo", "foo1" ]
GROUP BY t.uid
LETTING m = MAX([t.time,META(t).id])
ORDER BY m[0] DESC;
1 Like
4

Thank you both for the excellent examples. All three work well. I decided to go with fetching the keys in the first query and the documents in a second query. This is approach has been much faster in my testing and also lets me use a projection in my second query.

1 Like
5

Hi @todd,

You can try the following single query

SELECT d.* FROM (SELECT  RAW (MAX([t.time,META(t).id])[1]) 
                  FROM mybucket AS t
                  WHERE t.api == "testkey" AND t.kind == "record" AND 
                                 t.uid IN [ "todd", "foo", "foo1" ]
                  GROUP BY t.uid) AS q 
JOIN mybucket AS d ON KEYS q
ORDER BY d.time DESC;
6

@vsr1
Thank you for the updated query. It is a very nice solution! I’ve used it as is and have adapted it to perform a time bounded query. The results so far are looking good. I’m updating the data set with 100k more records and will report back the results. Thanks again for taking the time to help.

Todd

1 Like