66492
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 <strong>first instance</strong> 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?
Answer1: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'
)
Answer2:Here is the solution to what you asked for,
SELECT account_id,full_name,Superhero
FROM(
SELECT p.*,(
CASE account_id
WHEN @curType
THEN @curRow := @curRow + 1
ELSE @curRow := 1 AND @curType := account_id END
) + 1 AS rank
FROM test p, (SELECT @curRow := 0, @curType := '', @curRank := 0) r) A
WHERE rank=2;
<a href="http://sqlfiddle.com/#!2/0b9ab/1" rel="nofollow"><strong>Sql_Demo</strong></a>
Output:
ACCOUNT_ID FULL_NAME SUPERHERO
1 Batman 1
2 Spiderman 1
3 Professor X 1
4 Cyclops 1
Answer3:try this
select * from table1
group by account_id
<a href="http://sqlfiddle.com/#!2/9bfc1/3" rel="nofollow">demo here</a>
this will give you the first of every account_id
