Issues running Max function and Order by in snowflake sql

Question

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

id	datetime	ip
111	2022-02-03 11:37:58:000	62.44.134.97
222	2021-02-03 11:37:58:000	88.44.134.96
111	2022-02-01 11:37:58:000	22.44.134.97
111	2021-02-03 11:37:58:000	69.44.134.97

what I want to achieve

id	datetime	ip
111	2022-02-03 11:37:58:000	62.44.134.97
222	2021-02-03 11:37:58:000	88.44.134.96

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

Answer 1

Michael's answer is good, but if you have many rows and wish to pick some rowa and exclude the rest, then the QUALIFY option works very nicely, and using ROW_NUMBER to choose the preferred row per set.

SELECT *
FROM VALUES 
    (111, '2022-02-03 11:37:58:000', '62.44.134.97'),
    (222, '2021-02-03 11:37:58:000', '88.44.134.96'),
    (111, '2022-02-01 11:37:58:000', '22.44.134.97'),
    (111, '2021-02-03 11:37:58:000', '69.44.134.97')
    v(id, datetime, ip_address)
QUALIFY row_number() over(partition by id order by datetime desc) = 1

thus for you table, and columns:

select id, datetime AS lastlogindatetime, "ip "
from final_extract
QUALIFY row_number() over ( partition by id order by datetime desc) = 1
order by 2 DESC
limit 10

is rather concise.