SQL Order By not working - SQL and Query to Grid Variable component

Matillion ETL
1

When using the Advanced mode of Query to Grid Variable, the 'order by' of my SQL query is not maintained.

 

When I run the query in Snowflake the order is as I have specified but when I "sample" or run the Query to Grid Variable component using the same query, the results are all over the place.

 

My query is using 'union' to smash some results into rows.

 

Another irritating inconsistency in Matillion.

 

Here is the SQL:

 

<code>

select 'SURVEY_ID' as cols, 0 as page_id, 1 as question_order union

select 'QUESTION_ID' as cols, 0 as page_id, 2 as question_order union

select 'RESPONSE_ID' as cols, 0 as page_id, 3 as question_order union

select 'RESPONSE_WEIGHT' as cols, 0 as page_id, 4 as question_order union 

select 'RESPONSE_STATUS' as cols, 0 as page_id, 5 as question_order union

select 'RESPONSE_DATE_CREATED' as cols, 0 as page_id, 6 as question_order union

 

  select 

    case when response_answer_secondary is not null then concat(response_question_text,'|',response_answer_primary)

      else response_question_text

    end as cols,

   q.page_id, q.question_order

 

  from research.survey_responses r

  left join research.survey_questions q on r.question_id = q.question_id

  where r.survey_id = 507570715

 

order by page_id, question_order

</ code>

2

I realized Snowflake is not using any kind of order when I "unioned" those selects together so adding the order by fields seemed to be the best way to force Snowflake to order the rows. Maybe this is a Snowflake issue more than Matillion?

3

The only place in Matillion ELT where you can specify the ORDER is in the data output components. For example, the Table Output Component (https://documentation.matillion.com/docs/1991942) has an ORDER BY property.

Usually, sorting your data is only relevant for humans who are looking at at a query result. Additionally, the order can be relevant for Snowflake's Mircopartiotions (the internal data structure). Both cases would only require the output components to sort the data - for intermediate results within the data transformation, this is not needed.

4

Cheers Michael,

​

The Query to Grid Variable component in "basic" mode offers an Order By field. However I need to write a SQL query in order to get the results I need. The order by in my query is not being honoured when populating the grid variable.

​

This is important because I'm using the grid variable to create a table. I need the table columns in a known order because I will be writing dynamic sql (ish) to pivot some data and push it into that table.

​

Just seems like the component should work the same in Basic or Advanced if I'm using an Order By clause.

​

Thanks 👍