I am new to snowflake and I am trying to run an sql query that would extract the maximum Datetime for each ID. Below is a sample of my data

Table name: final_extract

what I want to achieve

The code below doesn't seem to achieve what I want.

select "id", MAX("datetime") As LastLoginDateTime, "ip "
from final_extract
group by "id ","ip "
order by MAX("datetime") DESC
limit 10

CodePudding user response：

Your query doesn't work because you have a different ip for each row, you have to decide which to choose. Check it out, I choose the last value from ip:

SELECT DISTINCT id
     , MAX(datetime) OVER(PARTITION BY id) As LastLoginDateTime
     , LAST_VALUE(ip) OVER(PARTITION BY id ORDER BY datetime) AS ip
  FROM final_extract
 ORDER BY LastLoginDateTime DESC
 LIMIT 10;