MatBailie 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?

Answer

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
          );
Comments