The Problem

When you make a derived table, you often want to join it to another view. However, if this derived table does not have a primary key declared, the measures may not show up through the join. Read more here on why this occurs.

The Solution

You can use the row_number() window function in Postgres and Redshift databases to make a unique field, which can be used as a primary key. In MySQL, you can use a variable that iterates every row, to achieve the same effect.

Example

Postgres and Redshift

view: derived_table_name {
  derived_table {
    sql:
    SELECT
    row_number() OVER(ORDER BY created_at) AS prim_key,
    *
    FROM orders ;;
  }
  
  dimension: prim_key {
    type: number
    primary_key: yes
    sql: ${TABLE}.prim_key ;;
  }
}

MySQL

view: derived_table_name {
  derived_table {
    sql:
    SELECT
    CAST(@rownum := @rownum + 1 AS UNSIGNED) AS prim_key, t.*
    FROM orders t,
    (SELECT @rownum := 0) r ;;
  }
  
  dimension: prim_key {
    type: number
    primary_key: yes
    sql: ${TABLE}.prim_key ;;
  }
}