Use one column to group rows and the minimum of another column to order

I'm using kysely client to connect to a Postgres database, but you can also provide the solution in plain SQL.

You can understand the database structure by looking at this migration function:

await db.schema
  .createTable("BigPaint")
  .addColumn("id", "uuid", (col) =>
    col
      .primaryKey()
      .defaultTo(sql`gen_random_uuid()`)
      .notNull(),
  )
  .addColumn("createdAt", "timestamptz", (col) =>
    col.defaultTo(sql`now()`).notNull(),
  )
  .addColumn("name", "text", (col) => col.notNull())
  .execute();

await db.schema
  .createTable("Inspiration")
  .addColumn("id", "uuid", (col) =>
    col
      .primaryKey()
      .defaultTo(sql`gen_random_uuid()`)
      .notNull(),
  )
  .addColumn("createdAt", "timestamptz", (col) =>
    col.defaultTo(sql`now()`).notNull(),
  )
  .addColumn("bigPaintId", "uuid", (col) =>
    col.references("BigPaint.id").onDelete("cascade").notNull(),
  )
  .addColumn("traitId", "uuid", (col) =>
    col.defaultTo(sql`gen_random_uuid()`).notNull(),
  )
  .addColumn("content", "text", (col) => col.notNull())
  .execute();

await db.schema
  .createIndex("Inspiration_bigPaintIdIndex")
  .on("Inspiration")
  .columns(["bigPaintId"])
  .execute();

await db.schema
  .createView("InspirationView")

As you can see, in the last lines of code I'm creating a view. I want the Inspiration table to be reordered this way: the rows should be ordered by createdAt but the rows with the same traitId should be kind of like treated as only one row therefore one createdAt value. I think it's better to explain in with an example of ordered table.

I tried different queries using with, partitionBy, orderBy, but I only managed to correctly order by, at the end, at most only one column.

To order by the minimum "createdAt" per group of same "traitId" without actually aggregating rows, use a window function:

SELECT content, "createdAt", "traitId"
FROM   "Inspiration"
ORDER  BY min("createdAt") OVER (PARTITION BY "traitId"), "traitId", "createdAt", content;

You don't have to include the same expression in the SELECT list.

I added "traitId", "createdAt", content as (optional) additional ORDER BY expressions to break ties and make the order deterministic.

Aside: avoid those mixed-case identifiers to make your life with Postgres easier. See:

• Are PostgreSQL column names case-sensitive?