How to check multiple values in a column MySQL

Question

I have a column with values i.e.

number   id
1        111
1        111
3        222
4        222
5        333

I'm checking if the I.D has more than one instance and if the number is the same as the second or more instance. So here, it would return 1 because there is 2x 1's with id 111, but 222 doesn't return because 3 and 4 are not the same.

How would I do such a query?

I was told that I could do two queries or more and on the second or more query I would ignore the first instance.

it's just the query i need to do, I need to return an instance where the number occurs more than once — Brandon Ling
– Brandon Ling, Commented Nov 9, 2012 at 4:32

ajreal · Accepted Answer · 2012-11-09 04:23:07Z

2

select id, number
from the_table
group by id, number
having count(*)>1

you should have index defined on both id, and number column

ps: the order can be number, id ... depends on your composite index key

answered Nov 9, 2012 at 4:23

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brandon Ling Over a year ago

can you please explain in detail what you mean about the index?

ajreal Over a year ago

the index is like a shortcut key to get the matched rows. to create index: dev.mysql.com/doc/refman/5.0/en/create-index.html and further reading dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Abubakkar · Accepted Answer · 2012-11-09 04:20:07Z

0

you should try using group by clause and see whether the output is what you expect:

select number,id from table_name group by number;

answered Nov 9, 2012 at 4:20

Abubakkar

15.7k8 gold badges58 silver badges84 bronze badges

Comments

thar45 · Accepted Answer · 2012-11-09 04:22:11Z

0

GROUP BY DOCS

Select number,id from table_name group by number;

answered Nov 9, 2012 at 4:22

thar45

3,5705 gold badges35 silver badges49 bronze badges

1 Comment

Brandon Ling Over a year ago

Why does this not return the other values I don't understand. Don't you have to specify COUNT in this?

Collectives™ on Stack Overflow

How to check multiple values in a column MySQL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related