On 2018/06/10 1:41 AM, Joseph L. Casale wrote:
I have a table where I need to select all distinct records from two columns and
include a third that varies. It is possible that for multiple records where the 
and second columns are equivalent, the third varies so it cannot be used in the
distinct clause. In this case, I want to select the third column in the first 
and ignore the remaining to append to the final result.

For example:

colA | colB | colC
aaa | bbb | lorem ipsum
aaa | bbb | lorem ipsum dolar
aaa | ccc | foo bar

This should only return the first and third row.

How do you do this in SQLite?

DISTINCT is nothing more than a GROUP BY for the entire SELECT list. Problem is, you don't really want to group the entire select list, you only wish to group by the first two columns and then have a varying third column. I'm not sure how the value gets decided in your mind for the third column. What makes you say the   1st and 3rd rows must be shown? Why not the second and third rows? Is it simply because the row is first? or is there something about the "lorem ipsum" that is more suitable than the "lorem ipsum dolor" phrase?

I'm going to assume for simplicity that it just doesn't matter and you meant "any of the non-distinct values", so it could be stated then (in any SQL engine, not just sqlite):

SELECT colA, colB, MIN(colC) AS colC
  FROM t
 GROUP BY colA, colB

This will show rows 1 and 3 as asked.  Change the MIN to a MAX and it will show rows 2 and 3. There are other ways to manipulate the c column into the aggregate query, but since it probably doesn't matter I will stop here, however, feel free to ask if you have a more specific need.

Note: SQLite does let you get away with NOT having an aggregate function in an aggregate query on a non-grouped column - i.e. this will work too (but only in sqlite and with arbitrary choice of value for colC):

SELECT colA, colB, colC
  FROM t
 GROUP BY colA, colB


sqlite-users mailing list

Reply via email to