How to select the rows with duplicate values in a table?

Question

How does one select a certain column whose got values of same value in another column? In this case, the aim is to select the names of the people who teach in the same class. So how does one select the names of the peple who teach in the same class?

kcode kname name --------------- -------------------------------------------------- ---------- TIG098 eBusiness and eGovernment Agneta TIG015 Informationsteknologi och informationssystem Aida TIG058 Programmeringsteknik och databaser Alan TIG059 Systemutvecklingsprojekt Dina TIG163 Beslutsstodsystem Faramarz TIG166 Tillampad IT management Fredrik TIG167 Fordjupning i programmering Henrik TIG016 Verksamheter och information Janne TIG067 Examensarbete Johan TIG015 Informationsteknologi och informationssystem Jonas TIG164 Interaktionsdesign Juha TIG015 Informationsteknologi och informationssystem Kalle TIG015 Informationsteknologi och informationssystem Kjell TIG015 Informationsteknologi och informationssystem Lennart TIG098 eBusiness and eGovernment Lisen TIG015 Informationsteknologi och informationssystem Magnus TIG015 Informationsteknologi och informationssystem Maria TIG165 Informatik som vetenskap Marie TIG167 Fordjupning i programmering Rikard TIG015 Informationsteknologi och informationssystem Urban TIG165 Informatik som vetenskap William

The expected results should be that of:

Agneta Aida Henrik Jonas Kalle Kjell Lennart Lisen Magnus Maria Marie Rikard Urban William

Please show us the results that you expect for this sample data. — GMB
– GMB, Commented Mar 29, 2020 at 17:17

forpas · Accepted Answer · 2020-03-29 17:32:58Z

With EXISTS:

select t.name from tablename t where exists ( select 1 from tablename where name <> t.name and kcode = t.kcode ) order by t.name

See the demo.
Results:

| name | | ------- | | Agneta | | Aida | | Henrik | | Jonas | | Kalle | | Kjell | | Lennart | | Lisen | | Magnus | | Maria | | Marie | | Rikard | | Urban | | William |

Or with group_concat() for each class:

select kcode, kname, group_concat(name) names from tablename group by kcode, kname having count(*) > 1

See the demo.
Results:

| kcode | kname | names | | ------ | -------------------------------------------- | ------------------------------------------------- | | TIG015 | Informationsteknologi och informationssystem | Aida,Jonas,Kalle,Kjell,Lennart,Magnus,Maria,Urban | | TIG098 | eBusiness and eGovernment | Agneta,Lisen | | TIG165 | Informatik som vetenskap | Marie,William | | TIG167 | Fordjupning i programmering | Henrik,Rikard |

Is there another way to do this besides doing concat since i havent learnt how to use that yet
Sorry for the concern, but that one seems to be even more complicated. can you post the concat one again and ill mark it as solved thank you man

Collectives™ on Stack Overflow

How to select the rows with duplicate values in a table?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related