SQLite - SELECT DISTINCT of one column and get the others

Question

I have a table like this (but with more columns):

Code    Quantity         
-----   --------       
00001      1           
00002      1           
00002      1           
00002      2           
00003      2          
00003      1          

And I want to get the same result that with SELECT DISTINCT Code FROM table (00001,00002,00003) but with all of the other table columns.

UPDATED: If I perform this: SELECT DISTINCT Code, Quantity from table I get:

    Code    Quantity         
    -----   --------       
    00001      1           
    00002      1           
    00002      2           
    00003      1          
    00003      2  

And I would like to get:

    Code    Quantity         
    -----   --------       
    00001      1           
    00002      1                   
    00003      1 

Thanks in advance!

Answer 1

Assuming you are using MySQL (as the question is tagged), the following will return an arbitrary value for the other columns:

select *
from t
group by code;

However, the particular values being selected come from indeterminate rows.

Answer 2

Building up on Gordon's answer, you can order a sub-query so that the group by will always return the lowst quantity for each code.

select *
from (select * from t order by code desc, quantity asc)
group by code;

Answer 3

Deterministic min/max

To also guarantee that you will get the row with minimal quantity deterministically as in the desired output, you can simply add it as min(quantity) to the select as in:

select *, min(quantity)
from t
group by code;

As mentioned at: row with max value per group - SQLite

SQLite docs guarantee that this works https://www.sqlite.org/lang_select.html#bareagg

Special processing occurs when the aggregate function is either min() or max(). Example:

SELECT a, b, max(c) FROM tab1 GROUP BY a;

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum.

PostgreSQL

In PostgreSQL 13.5 you can't GROUP by columns that are not either aggregates or PRIMARY: Select first row in each GROUP BY group?

But PostgreSQL has the SELECT DISTINCT ON extension which solves the use case nicely: https://www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT as it allows you to specify separately what needs to be distinct and what needs to be returned:

select distinct on (code) *
from t
group by code
order by code ASC, quantity ASC

This query would also deterministically pick the entries with lowest quantity, since order by is respected when choosing which column to pick.

SELECT DISTINCT ON was WONTFIXed in SQLite: https://code.djangoproject.com/ticket/22696 While this simple case can be achieved by both DBMSs, SELECT DISTINCT is simply more general than SQLite's magic min/max currently, e.g. it can handle multiple columns which SQLite says is not possible with its implementation.