CODEWITHSUNDEEP
mysqlsql
Douglas Gaskell
Douglas Gaskell

Reputation: 10060

MySQL return prioritizes value else return other value

I have a table (This is a mock-table),

+------------+------+--------+
|    Name    | Code | Active |
+------------+------+--------+
| Sales      |   55 |      1 |
| Sales      |   55 |      0 |
| IT         |   22 |      1 |
| Production |   33 |      1 |
| Production |   33 |      0 |
| Marketing  |   77 |      0 |
| Marketing  |   77 |      0 |
+------------+------+--------+

And I want to return a list of distinct names and codes. However, I want to determine if the department is active or not. so if Sales has a 1 in active and a 0 in active they are active, but is they had only zeros then they are not.

I've tried a variety of methods and read through a few dozen SO post, but am not gaining any progress.

The output I am trying to achieve is:

+------------+------+--------+
|    Name    | Code | Active |
+------------+------+--------+
| Sales      |   55 |      1 |
| IT         |   22 |      1 |
| Production |   33 |      1 |
| Marketing  |   77 |      0 |
+------------+------+--------+

How can I prioritize the Active column to a value of 1, but still return an entry if all entries with the same code have a value of 0 (such as marketing)?

Upvotes: 0

Views: 22

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

GROUP BY name and code and get the maximum value of Active. (Assuming 0 and 1 are the possible values for Active column)

SELECT Name,Code,MAX(Active) active
FROM tablename
GROUP BY Name,Code

Upvotes: 2

Related Questions