CODEWITHSUNDEEP
sqlsql-server
timton
timton

Reputation: 61

Remove duplicate rows from joined table

I have following sql query 

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode

Which provide following result 

School| avgscore
xyz   |  5
xyz   |  5
xyz   |  5
abc   |  3
abc   |  3
kkk   |  1

My question is how to remove those duplicates and get only following.

 School| avgscore
    xyz   |  5 
    abc   |  3
    kkk   |  1

I tried with 

   SELECT m.School, c.avgscore 
   FROM postswithratings c 
   join ZEntrycriteria on c.fk_postID= m.schoolcode 
   group by m.School

But it gives me following error

"Column 'postswithratings.avgscore' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Upvotes: 3

Views: 97

Answers (6)

Jesse
Jesse

Reputation: 65

you only use group by if you're using aggregated function, eg. max. sum, avg

in that case, 

SELECT Distinct(m.School), c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode

Upvotes: 0

mansi
mansi

Reputation: 877

You have to only add distinct keyword like this :-

 SELECT DISTINCT  m.School, c.avgscore 
  FROM postswithratings c 
 join ZEntrycriteria on c.fk_postID= m.schoolcode

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

This will delete the Duplicate rows (Only Duplicate)

Schema:

CREATE TABLE #TAB (School varchar(5) , avgscore int)
INSERT INTO #TAB
SELECT 'xyz', 5
UNION ALL
SELECT 'xyz', 5
UNION ALL
SELECT 'xyz', 5
UNION ALL
SELECT 'abc', 3
UNION ALL
SELECT 'abc', 3
UNION ALL
SELECT 'kkk', 1

Now use CTE as your Tempprary View and delete the data.

;WITH CTE AS(
SELECT ROW_NUMBER() OVER (PARTITION BY School,avgscore  ORDER BY (SELECT 1)) DUP_C, 
School,  avgscore FROM #TAB
)

DELETE FROM CTE WHERE DUP_C>1

Now do check #TAB, the data will be

+--------+----------+
| School | avgscore |
+--------+----------+
| xyz    |        5 |
| abc    |        3 |
| kkk    |        1 |
+--------+----------+

Upvotes: 0

Tobias Frischknecht
Tobias Frischknecht

Reputation: 1

Using the DISTINCT keyword will make sql use sets instead of multisets. So values only appear once

Upvotes: 0

Georgi Raychev
Georgi Raychev

Reputation: 1334

No need to make things complicated. Just go with:

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 
group by m.School, c.avgscore

or

SELECT DISTINCT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode

Upvotes: 4

Chanukya
Chanukya

Reputation: 5893

CREATE TABLE #Table2
    ([School] varchar(3), [avgscore] int)

INSERT INTO #Table2
    ([School], [avgscore])
VALUES
    ('xyz', 5),
    ('xyz', 5),
    ('xyz', 5),
    ('abc', 3),
    ('abc', 3),
    ('kkk', 1)
;
SELECT  SCHOOL,AVGSCORE FROM (SELECT *,ROW_NUMBER() OVER( PARTITION BY [AVGSCORE] ORDER BY (SELECT NULL)) AS RN FROM #TABLE2)A
WHERE RN=1
ORDER BY AVGSCORE
-------
;WITH CTE AS
(SELECT *,ROW_NUMBER() OVER( PARTITION BY [AVGSCORE] ORDER BY (SELECT NULL)) AS RN FROM #TABLE2)
SELECT SCHOOL,AVGSCORE  FROM CTE WHERE RN=1

output

SCHOOL  AVGSCORE
kkk      1
abc      3
xyz      5

Upvotes: 0

Related Questions