CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-bydistinct
BlueFox
BlueFox

Reputation: 171

MYSQL: SELECT DISTINCT only show rows which are unique

I only want to see the rows where the column 'Stadt'(=City) isn´t equal to 'Hauptstadt'(=Capital). my table looks like:

+----------------------+-------------------+--------------------------+---------------+
| Name                 | Stadt             | Land                     | Hauptstadt    |
+----------------------+-------------------+--------------------------+---------------+
| Kunstmuseum          | Bern              | Schweiz                  | Bern          |
| Musée Picasso        | Paris             | Frankreich               | Paris         |
| Museum Ludwig        | Köln              | Deutschland              | Berlin        |
| Museum of Modern Art | New York          | United States of America | Washington DC |
| Städel               | Frankfurt am Main | Deutschland              | Berlin        |
+----------------------+-------------------+--------------------------+---------------+

I only want to see(desired output):

+----------------------+-------------------+--------------------------+---------------+
| Name                 | Stadt             | Land                     | Hauptstadt    |
+----------------------+-------------------+--------------------------+---------------+
| Museum Ludwig        | Köln              | Deutschland              | Berlin        |
| Museum of Modern Art | New York          | United States of America | Washington DC |
| Städel               | Frankfurt am Main | Deutschland              | Berlin        |
+----------------------+-------------------+--------------------------+---------------+

I tried it this:

SELECT DISTINCT Name, Stadt, Land ,Hauptstadt 
FROM Museum 
GROUP BY 'Name', 'Stadt', 'Land', 'Hauptstadt' 
ORDER BY Name;

Output:

+----------------------+----------+--------------------------+---------------+
| Name                 | Stadt    | Land                     | Hauptstadt    |
+----------------------+----------+--------------------------+---------------+
| Museum of Modern Art | New York | United States of America | Washington DC |
+----------------------+----------+--------------------------+---------------+

How should my SELECT look like to get my desired ouput?

Thanks for your help!

Upvotes: 0

Views: 127

Answers (2)

wrecklez
wrecklez

Reputation: 347

we need to group it by name and just compare the two columns if is equal or not

SELECT * FROM Museum WHERE Stadt != Hauptstadt GROUP BY Name;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The single quotes in the group by are messing you up. First, only use single quotes for string and date constants. Second, you almost never need group by with select distinct. Third, you need a where clause for your condition. So try this:

SELECT DISTINCT Name, Stadt, Land ,Hauptstadt 
FROM Museum 
WHERE Stadt <> Hauptstadt
ORDER BY Name;

Upvotes: 1

Related Questions