I’m stuck trying to solve a problem using SQL (MySQL 5.6). Need to get the address which has ID 2 or, if not exists ID 2, the lower ID.
For example
x
|-----------------|---------------|
| CostumerID | AddressID |
|-----------------|---------------|
| 1 | 4 |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 4 |
| 4 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 4 |
| 7 | 2 |
| 7 | 4 |
| 8 | 3 |
| 9 | 1 |
| 9 | 3 |
| 9 | 4 |
| 9 | 2 |
|-----------------|---------------|
If a costumerID have an AddressID 2, must get that. If not, must get the minimum AddressID. The output must be like:
|-----------------|---------------|
| CostumerID | AddressID |
|-----------------|---------------|
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 3 |
| 5 | 2 |
| 6 | 4 |
| 7 | 2 |
| 8 | 3 |
| 9 | 2 |
|-----------------|---------------|
So far I’ve tried this:
SELECT distinct CostumerID,
if (AddressID= 2, AddressID,
(select min(b.AddressID) from Addresses b where b.AddressID= a.AddressID)) as tipus
FROM from Addresses a
but get duplicates at CostumerID.
Advertisement
Answer
Use aggregation with CASE
logic:
SELECT
CostumerID,
CASE WHEN COUNT(CASE WHEN AddressID = 2 THEN 1 END) > 0
THEN 2 ELSE MIN(AddressID) END AS AddressID
FROM yourTable
GROUP BY
CostumerID;