CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
MatBailie
MatBailie

Reputation: 86735

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?

Upvotes: 1

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 1

Related Questions