I am new to snowflake and I am trying to run some sql query that pulls out the latest date and its related ip

table name: lex

what I am trying to achieve

what I did so far

Select id,
       date,
       ip
From lex
where date = (Select Max(date) from lex)

This code only gives me one row instead of three

CodePudding user response：

Using QUALIFY:

SELECT *
FROM lex
QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC) = 1;

If it is possible to have more than one row per id at the exact same time the RANK should be used instead.

CodePudding user response：

I thought "most recent" is defined by the date-column, but that's not true. "Most recent" is in your case defined on the row order, isn't it? For example for ID 001 the date-column is the same for the first two records and you just pick the first occurence.

If that's the case, it cannot be solved in a deterministic way with SQL based on above input table. A result set will be retrieved and result sets have no order (unless order by is applied).

My recommendation: Add a new SQL column, which is showing the exact insert date timestamp and from then it can be solved.

Lets say the value is a typo, then please try:

select * from lex a
where date = (select max(date) from lex b where a.ip = b.ip)