I have a table of commits from various repos. I want to select all the users, and get at most one repo that they are associated to.
My attempt:
SELECT distinct(author_id), repo_id FROM commits This returns more rows than expected though, with duplicates of author_id's.
Use distinct on.
As per the documentation
DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.
SELECT distinct on (author_id) author, repo_id FROM commits order by author 
SELECT distinct(author_id), repo_id FROM commits GROUP BY author_id for MYSQL
