CODEWITHSUNDEEP
sqllistsnowflake-cloud-data-platform
user18466310
user18466310

Reputation: 81

How to query from list SQL

I am running the line

LIST @my_stage;

to get a list of all of the files that have been staged. Is there a way that I can get the most recent file that has been staged? The output shows the files in ascending date order so I would like to grab the last file in this list. Is there a way I can do this? (or something similar)

Upvotes: 0

Views: 83

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

Nick is correct but sometimes working examples help:

list @CITIBIKE_TRIPS;

enter image description here

select count(*) as file_count
from table(result_scan(LAST_QUERY_ID()));
FILE_COUNT
4,227

now to be honest, I was fiddling around trying to sum the file size etc etc, so swapped to this code

list @CITIBIKE_TRIPS;

set id = (select LAST_QUERY_ID());

select *
from table(result_scan($id));

select count(*) as file_count
from table(result_scan($id));

while playing so I could keep referring to the same query..

Upvotes: 0

NickW
NickW

Reputation: 9778

Just use RESULT_SCAN to process the resultset of the list command https://docs.snowflake.com/en/sql-reference/functions/result_scan.html

Upvotes: 1

Related Questions