Sqlite Select Query Across Multiple Columns With Duplicate Grouped Rows

I'm not entirely sure how I can put together a single SQLite query to achieve the following. I can get bits and pieces to work, but can't seem to meld it all into one single one. I

Solution 1:

To me, it looks like you want the minimum value of ColumnD for pairs of ColumnA and ColumnB. If you don't care about the id or ColumnC, a simple group by is sufficient:

select ColumnA, ColumnB, min(ColumnD)
fromtable t
groupby ColumnA, ColumnB;

If you do need all the values in the row, you can join back to get them:

select t.*
from table t join
     (select ColumnA, ColumnB, min(ColumnD) as ColumnD
      from table t
      groupby ColumnA, ColumnB
     ) tt
     on t.ColumnA = tt.ColumnA and t.ColumnB = tt.ColumnB and
        t.ColumnD = tt.ColumnD;

This assumes that ColumnD is never duplicated for values in ColumnA and ColumnB.

Solution 2:

You should be able to use a GROUP BY clause to group the fields that you want to collate rows for and perform aggregate calculations on:

SELECT ColumnA, ColumnB, Min(ColumnC), Min(ColumnD)
FROM Table1
GROUPBY ColumnA, ColumnB