CODEWITHSUNDEEP
sql-server
user4279558
user4279558

Reputation:

Duplications in Query Result

I am working on SQL Server to organize the generated data in C#. However, when I check the data in the table, there is a problem.

Here is the sample results:

Title                           Author Names                      with  w/o
Estimating the usefulness ...   W Meng KL Liu C Yu W Wu N Rishe     71  64

Estimating the usefulness ...   W Meng KL Liu C Yu W Wu N Rishe     71  58

Estimating the usefulness ...   W Meng KL Liu C Yu W Wu N Rishe     71  54

Estimating the usefulness ...   W Meng KL Liu C Yu W Wu N Rishe     71  53

The effect of negation ...      L Jia  C Yu   W Meng                66  65

The effect of negation ...      L Jia  C Yu   W Meng                66  65

The effect of negation ...      L Jia  C Yu   W Meng                66  65

The effect of negation ...      L Jia  C Yu   W Meng                66  65

What kind of query should I write to get this result:

Estimating the usefulness ...   W Meng KL Liu C Yu W Wu N Rishe     71  53

The effect of negation ...      L Jia  C Yu   W Meng                66  65

Thank you for your time and help.

Note: Distinct does not work for this.

Upvotes: 0

Views: 45

Answers (2)

Rogala
Rogala

Reputation: 2773

I would use a group by statement.

SELECT [Title]
     ,[Author Names]
     ,AVG([with]) as [Avg With]
     ,AVG([w/o]) as [w/o] 
FROM [table name here]
GROUP BY [title]
     ,[Author]

Of course, you will replace your aggregate function with what you need.

Upvotes: 1

sqluser
sqluser

Reputation: 5672

You can use a window function to limit your result in a CTE and then make a query based on that.

;WITH C AS(
    SELECT ROW_NUMBER() OVER(PARTITION BY Title , [Author Names] ORDER BY Title , [Author Names], [w/o]) AS Rn
            ,Title, [Author Names], [With], [w/o]
    FROM @tbl
)
SELECT * FROM C
WHERE Rn = 1

Upvotes: 0

Related Questions