MYSQL Get column group with different values on other column

Question

I have the following table:

week status
------- ------
102014 1
102014 1
102014 1
112014 2
112014 2
112014 2
122014 2
122014 0
122014 0
132014 1
132014 1
132014 1
142014 2
142014 2
142014 2

How can I get 122014 which has different values on status?

• ypercubeᵀᴹ: select week from tablename group by week where min(status) < max(status);

Answer 1

SELECT week, GROUP_CONCAT(DISTINCT status)
 FROM tbl
 GROUP BY week
 HAVING COUNT(DISTINCT status) > 1;

Answer 2

I'm not entirely sure I understand your question but I'm assuming you want to select distinct values when week=122014. The keyword being DISTINCT. Here's what your query would look like assuming your table was called status_report:

mysql> select distinct(week),status from status_report where week=122014;
+--------+--------+
| week | status |
+--------+--------+
| 122014 | 2 |
| 122014 | 0 |
+--------+--------+
2 rows in set (0.00 sec)

• RobertoNovelo: The problem is that I do not know the week beforehand. If you remove the where condition in your query, I would also get the other week values.
• tim berspine: Yes, removing the where condition would give you distinct rows for all possible combinations of (week, status). What is your use case? Maybe you could add that information (how you are using this query) to your question?