How to display recent timestamp records and ignore the previous received timestamp records in SQL(databricks)

Question

select * from XYZ, the below shows the result set.

timestamp       |    Name
20221006050109  |    A 
20221006050109  |    B
20221006050109  |    C
20221006050109  |    D
20221007050125  |    E
20221007050125  |    F
20221007050125  |    G
20221007050125  |    H

My output should ignore the old timestamp and give me the records with latest timestamp

timestamp        |   Name    
20221007050125   |   E
20221007050125   |   F
20221007050125   |   G
20221007050125   |   H

i am doing this in Databricks on Hive table. i do not want to do with max(timestmap) since max is aggregation function and i explicitly want to ignore the data with old timestamp first then pick the data with latest timestamp.

Answer 1

You can use RANK function as approach here:

WITH ranked AS (
    SELECT timestamp, name,  RANK() OVER (ORDER BY timestamp DESC) rnk
    FROM tbl
) SELECT * FROM ranked WHERE rnk = 1; 

sql editor online