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!

What should be the output value in all the other columns? So from your own example, if Code is 00003 then what should be the Quantity (2, 1, or something else)? — Aziz Shaikh, Jun 24 '13 at 13:52
So... you want the lowest `Quantity`, or the first occurrence? — Math, Jun 24 '13 at 14:00

score 43 · Accepted Answer · answered Jun 24 '13 at 13:56

43

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.

answered Jun 24 '13 at 13:56

Gordon Linoff

1,198,228
53
572
709

4

The question is for SQLite; the answer is the same regardless. – lilbyrdie Apr 28 '14 at 15:43
@lilbyrdie Gordon is right, even when he is wrong. – Ciro Santilli Путлер Капут 六四事 Apr 19 '22 at 10:29

score 4 · Answer 2 · answered Mar 09 '21 at 14:13

4

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;

answered Mar 09 '21 at 14:13

Adrien

51
2

The subquery is not needed for SQLite which magically guarantees that `min` and `max` will return the matching column: https://stackoverflow.com/a/71924314/895245 – Ciro Santilli Путлер Капут 六四事 Apr 19 '22 at 11:32

Ciro Santilli Путлер Капут 六四事 · Answer 3 · 2022-04-19T15:07:05.667

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.

SQLite - SELECT DISTINCT of one column and get the others

3 Answers3

Linked