SQLite: remove duplicate rows when entire row is not unique

Question

I'm creating an SQLite view to track customer emails and their accounts. There are some customers who created multiple accounts with the same email address. Using SQLite, is there a way to restrict the view to return each email only once regardless of how many accounts that individual has? I am not concerned which account gets discarded (i.e. Jim1 or Jim2).

AccountName    Email
Jim1           Jim@example.com
Jim2           Jim@example.com
Shania         Sh@example.com

The view would return:

AccountName    Email
Jim1           Jim@example.com
Shania         Sh@example.com

Thanks!

Answer 1

Group the results by Email, then take the first or last corresponding record:

  SELECT Email
  ,      MIN(AccountName) AS Account
    FROM table
GROUP BY Email;

UPDATE: Note that you can add a unique constraint to the email column, so that SQLite errors out if you try to insert the same value a second time:

CREATE TABLE Users
( id          INTEGER PRIMARY KEY
, email       TEXT    NOT NULL UNIQUE COLLATE NOCASE
, accountname TEXT    NOT NULL        COLLATE NOCASE
);

The UNIQUE clause on the email column indicates that no two rows can contain the same value.
The COLLATE NOCASE clause causes SQLite to compare values in that row in a case-insensitive manner (so 'EMAIL' and 'eMail' would count as equal).