CODEWITHSUNDEEP
mysqlsql
Nebur
Nebur

Reputation: 3

Select ans specific value or, if not exists, the minimum one

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

|-----------------|---------------|
|  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.

Upvotes: 0

Views: 32

Answers (2)

user13722723
user13722723

Reputation: 

SELECT CASE
     WHEN EXISTS(SELECT *
                 FROM   tbl_name
                 WHERE  AddressID = 2)
       THEN (SELECT *
             FROM   tbl_name
             WHERE  AddressID > 2 )
     ELSE 'Default Value'
   END

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

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;

Upvotes: 1

Related Questions