How to remove duplicate values from a query

Question

I have the following issue, I need to remove duplicate values from a specific column I query. No deleting!(ClassID)

SchoolNo	Schoolyear	Schoolgrade	Classname	ClassId
65432	2001	5	ab	441
65432	2001	5	cd	442
65432	2001	6	a	443
65432	2001	6	b	444
56838	2001	5	ab	445
56838	2001	5	cd	446
56838	2001	6	ab	445
56838	2001	6	ef	447
12726	2001	5	ms	448
12726	2001	6	ms	448

If you look at the values of classId I have repeated class numbers because some special schools sometimes put 2 classes together for both grades. The problem is my query needs to show only 1 classid value. No repeats. Therefore we can remove any extra class that is repeated in value and only show for grade 5.

In other words my table should at end up looking like this.

SchoolNo	Schoolyear	Schoolgrade	Classname	ClassId
65432	2001	5	ab	441
65432	2001	5	cd	442
65432	2001	6	a	443
65432	2001	6	b	444
56838	2001	5	ab	445
56838	2001	5	cd	446
56838	2001	6	ef	447
12726	2001	5	ms	448

The code generally looks like this.

select schoolno,schoolyear,schoolgrade,classname,classId
from classgroup cg

How should I approach this?

NuLo · Accepted Answer · 2021-09-14 17:03:44Z

2

maybe you can do it like this:

select
  first_value(schoolno) over w,
  first_value(schoolyear) over w,
  first_value(schoolgrade) over w,
  first_value(classname) over w,
  first_value(classId) over w
FROM
  classgroup
WINDOW w AS (PARTITION BY schoolno, schoolyear, classId ORDER BY schoolgrade);

You partition the data by schoolno, schoolyear and classId and order by schoolgrade then take only the first row of each partition.

Note: the syntax may be a bit off since I couldn't test it

answered Sep 14, 2021 at 17:03

NuLo

1,5381 gold badge13 silver badges19 bronze badges

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

2 Comments

Mitko Keckaroski Over a year ago

I did a test on your query and it is OK. Nice approach.

Tito Over a year ago

I have given it a try but i got the same result... it is not removing the 2nd row of similar classID the repeated classid is showing twice.

Mitko Keckaroski · Accepted Answer · 2021-09-14 17:47:18Z

1

Try this

select cg1.* from classgroup cg1
left join classgroup cg2 on (cg1."ClassId"=cg2."ClassId" and cg1."Schoolgrade"<cg2."Schoolgrade")
where cg2."Schoolgrade" is null

The output:

answered Sep 14, 2021 at 17:47

Mitko Keckaroski

1,0891 gold badge10 silver badges16 bronze badges

3 Comments

Tito Over a year ago

Hey so your query worked in the case that repeated classid's dont repeat themselves. However I am getting instead of a 5 a 6 even through the logic sounds fine. is there a way to apply a case statement to force it?

Tito Over a year ago

your method was perfect. Quick question what is the "" for? is there a difference between cg2.schoolgrade and scg."schoolgrade"?

Mitko Keckaroski Over a year ago

Use "" when column names contain capital letters.

Collectives™ on Stack Overflow

How to remove duplicate values from a query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related