SQL: Get list of names form database without double occurences

Question

I am doing my first experiences with sql. I am currently working on a genealogical website feeded by an sql database. By the time the number of persons in the db has increased an I want to implement a short way to navigate to a specific person by a few clicks. Therefore, I want to first give a list of all family names within the database. The sql for this is quite simple:

SELECT familyname FROM tablewithdata ORDER BY familyname;

This obviously gives me a list like this:

Griffin
Griffin
Griffin
Miller
Miller
Miller
Sanchez
Sanchez
Simpson
Simpson
Simpson
Simpson ...

The occurrences of the family names are as much as the individuals with this name in the db.

But I want to have only one occurrence of each family name in the database like this:

Griffin
Miller
Sanchez
Simpson

I hope there is a simple solution for my request. My Google search only resulted in ways I can remove the double entries from my database.

Thank you very much for your help!!

Possible duplicate of [SQL How to remove duplicates within select query?](https://stackoverflow.com/questions/3695369/sql-how-to-remove-duplicates-within-select-query) — anasmi, Oct 21 '19 at 05:33
Do you want to just _view_ your data this way, or do you actually want to remove "duplicates," however you have defined them? — Tim Biegeleisen, Oct 21 '19 at 05:36

score 1 · Answer 1 · answered Oct 21 '19 at 05:29

1

Use select distinct familyname FROM tablewithdata ORDER BY familyname;

Hope this will help you.

answered Oct 21 '19 at 05:29

Arif Sher Khan

461
4
12

Vignesh Kumar A · Answer 2 · 2019-10-21T05:39:21.483

1

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

SELECT DISTINCT familyname 
FROM tablewithdata 
ORDER BY familyname;

To remove duplicate entries from database, you can use ROW_Number() concept here.

DELETE FROM
(
  SELECT familyname, ROW_NUMBER() OVER(PARTTION BY familyname ORDER BY familyname) 
  RN
  FROM tablewithdata;
) X WHERE RN > 1

edited Oct 21 '19 at 05:39

answered Oct 21 '19 at 05:36

Vignesh Kumar A

27,863
13
63
115

Your delete query looks to be Oracle, and in any case would be database specific. – Tim Biegeleisen Oct 21 '19 at 05:43

score 1 · Answer 3 · answered Oct 21 '19 at 05:41

1

you can use Group By:

SELECT familyname FROM tablewithdata GROUP BY familyname ORDER BY familyname

answered Oct 21 '19 at 05:41

Frank_Vr

661
7
23

1

Great! This Worked!! – Cpt. Hirni Oct 21 '19 at 05:52

score 0 · Answer 4 · answered Oct 21 '19 at 07:38

0

SELECT distinct(familyname) FROM tablewithdata ORDER BY familyname

. This should help you.

answered Oct 21 '19 at 07:38

Gaurav Dhiman

953
6
11

SQL: Get list of names form database without double occurences

4 Answers4