Removing duplicate rows from a concatenated table
I have the following sql query
SELECT m.School, c.avgscore
FROM postswithratings c
join ZEntrycriteria on c.fk_postID= m.schoolcode
Which gives the following result
School| avgscore
xyz | 5
xyz | 5
xyz | 5
abc | 3
abc | 3
kkk | 1
My question is how to remove these duplicates and only get the 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 the following error
"The column postswithratings.avgscore 'is not valid in the select list because it is not contained in either an aggregate function or a GROUP BY clause."
+3
source to share
6 answers
There is no need to complicate the situation. 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
+4
source to share
This will remove duplicate lines (duplicate only)
Scheme:
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 Tempprary View parameter and delete 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 check #TAB, data will be
+--------+----------+
| School | avgscore |
+--------+----------+
| xyz | 5 |
| abc | 3 |
| kkk | 1 |
+--------+----------+
0
source to share
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
0
source to share