Duplicate values in a SQL table

duplicatesmysql> use histology_db;
Database changed
mysql> SELECT word, count(word)
-> FROM dictionary
-> GROUP BY word
-> HAVING count(word) > 1;

create_viewCREATE VIEW duplicates AS

create_view_verbsCREATE VIEW verbs AS …

duplicate_verbsmysql> SELECT d.word_ID, LEFT(d.word, 10), d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word;
+———+——————+———-+
| word_ID | LEFT(d.word, 10) | category |
+———+——————+———-+
| 15 | study | verb |
| 19 | study | noun |
| 57 | focus | noun |
| 160 | advance | noun |
| 183 | focus | verb |
| 193 | section | noun |
| 247 | section | verb |
| 343 | advance | Verb |
+———+——————+———-+

beam_of_electronsmysql> SELECT d.word_ID, LEFT(d.word, 10) AS multiple, d.category
-> FROM dictionary d
-> INNER JOIN (SELECT word
-> FROM dictionary
-> GROUP BY word
-> HAVING (count(word_ID) > 1) ) v
-> ON d.word = v.word
-> ORDER BY multiple, d.category;

http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table
http://www.w3schools.com/sql/sql_join.asp

mysql> SELECT category, count(word_ID)
-> FROM dictionary
-> GROUP BY category;
+————–+—————-+
| category | count(word_ID) |
+————–+—————-+
| adjective | 58 |
| adverb | 8 |
| conjunction | 3 |
| determiner | 1 |
| noun | 201 |
| unclassified | 52 |
| verb | 61 |
+————–+—————-+

INNER JOIN
http://dev.mysql.com/doc/refman/5.6/en/example-foreign-keys.html

duplicate_values_and_conditionFinding a duplicate value, one of which has category=”unclassified”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s