php - Distinct on only one column - only display FIRST duplicate row

Question

I'm using an SQL query in PHP to display 5 columns from my "users" table. Here is my code for that:

SELECT DISTINCT account_id, full_name, email, login, phone, updated_at, last_request_at, unconfirmed_email FROM $table WHERE id < '300'

Basically, this table has 2 types of user - admin accounts and what we'll call "subusers" - they're still users but they don't have admin privileges, and they were created by their parent admin accounts. Because of this, the parent admin account and its subusers all share the same "account_id" Here's an example (sorry it's rubbish):

| account_id | full_name   | Superhero?  |
| 1          | Batman      |  1          |
| 1          | Robin       |  1          |
| 1          | Magneto     |  0          |
| 2          | Spiderman   |  1          |
| 2          | The Hulk    |  1          |
| 2          | Wolverine   |  1          |
| 3          | Professor X |  1          |
| 4          | Cyclops     |  1          |
| 4          | Shrek       |  0          |
| 4          | Superman    |  1          |
| 4          | Bob         |  0          |

So you can pretend that Spiderman made The Hulk's and Wolverine's accounts. This tells me that Spiderman has an admin account because he's the first instance of the account_id "2".

So as you can see, while full_name is unique, there are many duplicate account_id's, I would like to refine it so that that it only displays the first instance of each ID - there are only 4 different account_id's so it should only show 4 entries, like so:

| account_id | full_name   | Superhero?  |
| 1          | Batman      |  1          |
| 2          | Spiderman   |  1          |
| 3          | Professor X |  1          |
| 4          | Cyclops     |  1          |

How can I achieve this?

Answer 1

You should probably add another column. Now it is possible to get distinct record for every account_id using GROUP BY clause but results of all nonagreggated columns can be ambigius. You have to have some order you approve or indicator inside group to determine which record for every accout_id is "first". With column marking which record in each group is first query is simple. Without it you have to accept some order telling query which record is "first". On example alphabetical order of full_name:

SELECT account_id, 
       full_name, 
       email, 
       login, 
       phone, 
       updated_at, 
       last_request_at, 
       unconfirmed_email 
  FROM table1 WHERE full_name IN (
    SELECT MIN(full_name) 
      FROM table1 
      GROUP BY account_id 
      WHERE id < '300'
  )