Need help with DISCINT sql keyword.

Question

Hello.
I need your help with sql. Consider having following table named scores:
date name score gender
2018-4-12 Liz 98 f
2017-8-2 Max 85 m
2018-5-18 Bob 82 m
2017-8-2 Max 73 m
2018-5-18 Bob 82 m

I want to delete duplicate records that have exact same date & name & score (record 3 & 5). I don't care about gender column, but I want to display it too. So I want the table to look like this:

date name score gender
2018-4-12 Liz 98 f
2017-8-2 Max 85 m
2018-5-18 Bob 82 m
2017-8-2 Max 73 m

How can I do it?

Answer 1

Hi Jan, I would use GROUP BY: select name, date, score, gender from scores group by name, date, score, gender

Answer 2

GROUP BY as sql-clause has the effect, that every combination of the mentioned attributs appears exactly once. You can combine it with functions like e.g. sum(), count() etc.

Answer 3

You could save the unique result of the select into a new table with "select name, date, gender, score into table newTable group by ...", delete the old table and rename the new one.

Answer 4

Thanks for your answer, but what does GROUP BY exactly do?

Answer 5

Great. I got it now. But this will only select and show me what I want to keep in my table. Hkw can I delete the rest?

Answer 6

Ok. Thanks for your help.

Answer 7

select distinct * from scores