One of the items that we’re asked to do as data professionals is to only take the first few hundred rows from a table. Heck, it’s important enough that I’ve actually already talked about it in SQL Server here and a few other places also listed in that article.
I’m sure you’re asking yourself “Sherpa, does Snowflake have the same abilities as SQL Server when it comes to TOP?” Well, my friend, let’s settle in as I attempt to answer that. (All y’all who know me knew that the answer was going to be “It Depends,” right?)
First, we have our basic TOP query.
SELECT TOP 100 * FROM public.employees;
Nothing flashy or fancy about this. But notice in SQL Server, especially in the newer versions, it wants me to use paranthesis around the 100. I try that in Snowflake and I get this ugly message:
Well, that’s not going to work.
If I try to do the top 100 PERCENT, I also get the following error:
So, what do we do now if we want the first half of the table? Or the top 10 percent of our sales staff?
Guess what, my friends? Snowflake has a separate function for that! In this case, it’s called SAMPLE! For those of you who didn’t look at this table with us last time, there are 290 rows in the table.
Yes! I told Snowflake to give me 10 percent of the data back – Yes, the percent is not typed in – and it did! Saves me from having to attempt to figure out percentages and whatnot.
If I want to have only the top 10 rows, I can slightly change the query from above (so that I don’t have to do lots of retyping or only have to remember one function) and get only the top 10 rows.
And with that I want to thank you for joining me yet again to talk about Snowflake and TOP (kinda) to see yet more of how Snowflake and SQL Server are alike and kinda different. Until next time my friends when we’ll go over more things about the TOP function!