SnowPro Associate: Platform Question 171
Single answer● Run basic SQL commandsA data analyst needs to quickly review the most recent 10 orders from the SALES.PUBLIC.ORDERS table to validate a dashboard issue. The analyst only needs the ORDER_ID, CUSTOMER_ID, and ORDER_DATE columns, and wants the results returned from newest to oldest by order date. Which SQL statement best meets this requirement in Snowflake?
- A
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE FROM SALES.PUBLIC.ORDERS ORDER BY ORDER_DATE DESC LIMIT 10;
- B
SELECT TOP 10 ORDER_ID, CUSTOMER_ID, ORDER_DATE FROM SALES.PUBLIC.ORDERS SORT BY ORDER_DATE DESC;
- C
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE FROM SALES.PUBLIC.ORDERS LIMIT 10 ORDER BY ORDER_DATE DESC;
- D
SELECT DISTINCT 10 ORDER_ID, CUSTOMER_ID, ORDER_DATE FROM SALES.PUBLIC.ORDERS ORDER BY ORDER_DATE DESC;
Show answer and explanation
Correct answer: A
Explanation
The correct answer is Option 1 because it follows Snowflake's SQL query pattern for returning a specific subset of ordered rows: SELECT the needed columns, ORDER BY the relevant column, and then LIMIT the number of rows returned. This is a common task when validating recent transactional data. In Snowflake documentation, ORDER BY is used to sort result sets, and LIMIT or FETCH restricts the number of rows returned. While Snowflake also supports TOP
- A. Correct.
Correct. This query uses standard Snowflake SQL syntax to return only the required columns, sorts the rows by ORDER_DATE in descending order so the newest records appear first, and then applies LIMIT 10 to return just the first 10 rows from that ordered result set. This is the appropriate way to retrieve a small, ordered sample of records in Snowflake.
- B. Incorrect.
Incorrect. Although Snowflake supports TOP n in a SELECT statement, the clause SORT BY is not the correct syntax for ordering final query results in Snowflake. The proper clause is ORDER BY. A candidate might choose this option because TOP 10 is familiar from other SQL dialects, but the invalid SORT BY makes the statement incorrect.
- C. Incorrect.
Incorrect. In Snowflake, ORDER BY must appear before LIMIT in the query. This option places LIMIT before ORDER BY, which is not valid query syntax. A common misconception is that LIMIT can be inserted earlier because it conceptually reduces rows, but SQL clause order matters.
- D. Incorrect.
Incorrect. DISTINCT removes duplicate rows based on the selected columns; it does not limit the result set to 10 rows. The syntax DISTINCT 10 is also invalid for this use case. Someone might pick this option if they confuse DISTINCT with limiting output volume.