MatBailie - 4 months ago 37
SQL Question

# SQLite - First Per Group - Composite Order & Opposing Sort Order

I'm looking for options on how to pick the first record per group, in SQLite, Where the sorting of the group is across a composite key.

Example Table:

`````` Key_1 | Sort1 | Sort2 | Val_1 | Val_2
-------+-------+-------+-------+-------
1   |   1   |   3   |   0   |   2
1   |   1   |   2   |   2   |   4
1   |   1   |   1   |   4   |   6
1   |   2   |   2   |   6   |   8
1   |   2   |   1   |   8   |   1
2   |   1   |   2   |   0   |   5
2   |   1   |   1   |   1   |   6
2   |   2   |   3   |   2   |   7
2   |   2   |   2   |   3   |   8
2   |   2   |   1   |   4   |   9
``````

Objective:

- Sort data by
`Key_1 ASC, Sort1 ASC, Sort2 DESC`

- Select first record per unique
`Key_1`

`````` Key_1 | Sort1 | Sort2 | Val_1 | Val_2
-------+-------+-------+-------+-------
1   |   1   |   3   |   0   |   2
2   |   1   |   2   |   0   |   5
``````

Analytic Function Solution...

`````` SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Key_1
ORDER BY Sort1,
Sort2 DESC
)
AS group_ordinal
FROM
table
)
sorted
WHERE
group_ordinal = 1
``````

Laborious ANSI-92 approach...

``````SELECT
table.*
FROM
table
INNER JOIN
(
SELECT
table.Key1, table.Sort1, MAX(table.Sort2) AS Sort2
FROM
table
INNER JOIN
(
SELECT
Key_1, MIN(Sort1)
FROM
table
GROUP BY
Key_1
)
first_Sort1
ON  table.Key_1 = first_Sort1.Key_1
AND table.Sort1 = first_Sort1.Sort1
GROUP BY
table.Key1, table.Sort1
)
first_Sort1_last_Sort2
ON  table.Key_1 = first_Sort1_last_Sort2.Key_1
AND table.Sort1 = first_Sort1_last_Sort2.Sort1
AND table.Sort2 = first_Sort1_last_Sort2.Sort2
``````

This involves a lot of nesting and self joins. Which is cumbersome enough when it involves just two sort columns.

My actual example has six sort columns.

I also would like to avoid anything like the following, as it is not (to my knowledge) guaranteed / deterministic...

``````SELECT
table.*
FROM
table
GROUP BY
table.Key_1
ORDER BY
MIN(table.Sort1),
MAX(table.Sort2)
``````

Are there any other options that I'm just not seeing?

I believe this will work in SQLite:

``````select t.*
from table t
where exists (select 1
from (select t2.*
from table t2
where t2.id = t.id
order by t2.sort1 asc, t2.sort2 desc
limit 1
) t2
where t2.sort1 = t.sort1 and t2.sort2 = t.sort2
);
``````

My concern is whether SQLite allows correlated references in nested subqueries. If not, you can just use `=` and concatenate the values together:

``````select t.*
from table t
where (sort1 || ':' || sort2) =
(select (sort1 || ':' || sort2)
from table t2
where t2.id = t.id
order by sort1 asc, sort2 desc
limit 1
);
``````