select and group by same value mysql

November 14, 2022

i have table with data like this below

| id | wallet_id | wallet_name | deposit |   |
|----|-----------|-------------|---------|---|
| 1  | 12        | a_wallet    | 10      |   |
| 2  | 14        | c_wallet    | 12      |   |
| 3  | 12        | a_wallet    | 24      |   |
| 4  | 15        | e_wallet    | 50      |   |
| 5  | 14        | c_wallet    | 10      |   |
| 6  | 15        | e_wallet    | 22      |   |

i want to select and group with same wallet_id, probably something like this

| wallet_id | id | wallet_name |
|-----------|----|-------------|
| 12        | 1  | a_wallet    |
|           | 3  | a_wallet    |
| 14        | 2  | c_wallet    |
|           | 5  | c_wallet    |
| 15        | 4  | e_wallet    |
|           | 6  | e_wallet    |

i already try

select wallet_id, id, wallet_name from wallet group by wallet_id

but it shows like usual select query with no grouping.

Kindly need your help, thanks

>Solution :

We would generally handle your requirement from the presentation layer (e.g. PHP), but if you happen to be using MySQL 8+, here is a way to do this directly from MySQL:

SELECT
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY wallet_id ORDER BY id) = 1
         THEN wallet_id END AS wallet_id,
    id,
    wallet_name
FROM wallet w
ORDER BY w.wallet_id, id;