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?
See Question&Answers more detail:os