Select multiple rows with the same value(s)

Question

I have a table, sort of like this:

ID  |  Chromosome | Locus | Symbol | Dominance |
===============================================
1   |      10     |   2   |   A    |   Full    |
2   |      10     |   2   |   a    |   Rec.    |
3   |      10     |   3   |   B    |   Full    |
4   |      10     |   3   |   b    |   Rec.    |

I'd like to select all rows with the same locus and chromosome. For example, rows 3 and 4. There may be more than 2 at a time and they may not be in order.

I tried this:

SELECT *
FROM Genes
GROUP BY Locus
HAVING Locus='3' AND Chromosome='10'

But it always returns row 3, never row 4, even when repeated. I think I'm missing something obvious and simple, but I'm at a loss.

Can someone help?

rows 1 and 2 also have the same locus and chromosome, what do you actually want as a result of your query? — Lamak
– Lamak, Commented Feb 10, 2012 at 18:44
Lamak--I want to select a certain locus and chromosome and get all the rows that match. — user1202673
– user1202673, Commented Feb 11, 2012 at 0:52

Community · Accepted Answer · 2012-04-16 23:37:35Z

54

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--show how many of each Locus there is
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus

--only show the groups that have more than one row in them
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1

--to just display all the rows for your condition, don't use GROUP BY or HAVING
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10'

edited Apr 16, 2012 at 23:37

CommunityBot

11 silver badge

answered Feb 10, 2012 at 19:09

KM.

104k34 gold badges183 silver badges214 bronze badges

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

1 Comment

user1202673 Over a year ago

So, yep the problem was with my php code, not my sql code. blush Went back to my original syntax of SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10' as so many suggested, fixed my php code, and all's right with the world! Knew it was something simple and obvious that I was missing! Thanks everyone!

Matt Fenwick · Accepted Answer · 2012-02-10 18:52:19Z

12

Assuming that you want all rows for which there is another row with the exact same Chromosome and Locus:

You can achieve this by joining the table to itself, but only returning the columns from one "side" of the join.

The trick is to set the join condition to "the same locus and chromosome":

select left.*
from Genes left
inner join Genes right
on left.Locus = right.Locus and 
  left.Chromosome = right.Chromosome and left.ID != right.ID

You can also easily extend this by adding a filter in a where-clause.

edited Feb 10, 2012 at 18:52

answered Feb 10, 2012 at 18:44

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

3 Comments

user3553260 Over a year ago

@Matt Fenwick Thanks so much! This helped out a lot.

Eric Over a year ago

FWIW this worked fine for me after I changed "left" to "l" and "right" to "r" -- maybe "left" is a reserved keyword in MariaDB where I'm using it, or something.

Libertine Makinta Over a year ago

Though using "left" or "right" doesn't work because they're reserved keywords, the use of inner joins here is the best solution. Thanks

JYelton · Accepted Answer · 2012-02-10 18:43:48Z

2

The problem is GROUP BY - if you group results by Locus, you only get one result per locus.

Try:

SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10';

If you prefer using HAVING syntax, then GROUP BY id or something that is not repeating in the result set.

answered Feb 10, 2012 at 18:43

JYelton

36.6k28 gold badges140 silver badges196 bronze badges

1 Comment

user1202673 Over a year ago

That's actually the original syntax I used. It and GROUP BY id both give the same result.

Sergey Kalinichenko · Accepted Answer · 2012-02-10 18:44:58Z

1

This may work for you:

select t1.*
from table t1
join (select t2.Chromosome, t2.Locus
    from table2
    group by t2.Chromosome, t2.Locus
    having count(*) > 1) u on u.Chromosome = t1.Chromosome and u.Locus = t1.Locus

answered Feb 10, 2012 at 18:44

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Comments

user359040 · Accepted Answer · 2012-02-10 18:53:01Z

0

One way of doing this is via an exists clause:

select * from genes g
where exists
(select null from genes g1
 where g.locus = g1.locus and g.chromosome = g1.chromosome and g.id <> g1.id)

Alternatively, in MySQL you can get a summary of all matching ids with a single table access, using group_concat:

select group_concat(id) matching_ids, chromosome, locus 
from genes
group by chromosome, locus
having count(*) > 1

answered Feb 10, 2012 at 18:53

user359040

Collectives™ on Stack Overflow

Select multiple rows with the same value(s)

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related