Student Table SID Name 1 A 2 B 3 C Marks Table id mark subject 1 50 physics 2 40 biology 1 50 chemistry 3 30 mathematics SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM student std JOIN marks m ON std.id=m.id AND m.mark=50
This result is 2 times A even after using disticnt . My expected result will have only one A. if i remove row_number() over() as rownum its working fine. Why this is happening ? how to resolve. AM using DB2!!
Advertisement
Answer
There are two rows in marks Table with id = 1 and mark = 50.. So you will get two rows in the output for each row in student table… If you only want one, you have to do a group By
SELECT std.id, std.name, m.mark, row_number() over() as rownum FROM student std JOIN marks m ON m.id=std.id AND m.mark=50 Group By std.id, std.name, m.mark