singlestorecolumnstore

Reading from MemSql (SingleStore) in case of multiple snapshots

I have a usecase where my memsql db will look something like this

DB- USER

country company user_id  passphrase timestamp
India   google  123      xyz        10:00AM
India   google  123      ABC        11:00AM
India   google  123      pqr        4:00PM
India   amazon  789      qwr        8:00AM
India   amazon  789      eer        10:00AM
India   amazon  789      yyt        5:00PM
Australia ..... and so on

my sort key would be (country,company)

now my use-case is for one country I want all the users and their passphrases, the condition being that I pick up the one with the latest timestamp. One way would be to run a query and get the latest timestamp for each user and then use it to get the latest data for each user, but that would be slow, is there any easier way using which memsql provides me the latest snapshot automatically without me doing the heavy lifting?

Solution

  • You can use the last_value window function to return the most recent passphrase for each user.

    Here's an example using your dataset:

    SELECT country, company, user_id , LAST_VALUE(passphrase) OVER(ORDER BY (passphrase))  as last_passphrase
FROM DB_user
GROUP BY 
country, company, user_id ;

    Link to the docs: https://docs.singlestore.com/managed-service/en/reference/sql-reference/window-functions/last_value.html