CODEWITHSUNDEEP
sql-server
FiverrAnimation
FiverrAnimation

Reputation: 3

SQL SERVER DUPLICATE VALUES

I have 2 tables, one for courses names (named Courses) and one for universities that teach the course (named Unicourses).

I want to select all the columns from UniCourses and also coursename column from Courses table.

Everything goes well but I have repeated values in the query result, and I want all coursename column to be unique.

I tried using DISTINCT but I can't manage to make it work.

this is the query I'm using:

SELECT 
    UniCourses.courseid, UniCourses.uniname, 
    UniCourses.rating, UniCourses.ratingppl, 
    UniCourses.approved,
    Courses.courseid, Courses.coursename  
FROM 
    UniCourses 
INNER JOIN 
    Courses ON UniCourses.courseid = Courses.courseid  
WHERE 
    UniCourses.approved = 1  
ORDER BY 
    UniCourses.rating / CASE UniCourses.ratingppl WHEN 0 THEN 1 ELSE UniCourses.ratingppl END DESC;

This is what I get

|courseid|uniname|rating|ratingppl|approved|coursename|
_______________________________________________________
|    4   |howard | 20   |    5    |    1   |  algebra |
|    4   |hampton| 10   |    2    |    1   |  algebra |

this is what I want

|courseid|uniname|rating|ratingppl|approved|coursename|
_______________________________________________________
|    4   |hampton| 10   |    2    |    1   |  algebra |

As you can see, these are the same course that 2 universities teach, I want only 1 to appear, that has the highest rating which is calculated by ( rating / ratingppl );

Courses table columns:

courseid, coursename

Unicourses table columns:

courseid, uniname, rating, ratingppl, approved

Upvotes: 0

Views: 41

Answers (3)

Yuriy A.
Yuriy A.

Reputation: 752

If there are no Id coulmns

SELECT
    UniCourses.courseid,
    UniCourses.uniname,
    UniCourses.rating,
    UniCourses.ratingppl,
    UniCourses.approved,
    Courses.courseid,
    Courses.coursename  
FROM UniCourses 
INNER JOIN Courses ON 
        Courses.courseid = UniCourses.courseid
INNER JOIN (
    SELECT 
        UniCourses.courseid, 
        MAX(UniCourses.rating / CASE UniCourses.ratingppl WHEN 0 THEN 1 ELSE UniCourses.ratingppl END) as MaxValue
    FROM UniCourses
    GROUP BY UniCourses.courseid
    ) AS CoursesMax ON 
            CoursesMax.courseid = UniCourses.courseid 
            AND (UniCourses.rating / CASE UniCourses.ratingppl WHEN 0 THEN 1 ELSE UniCourses.ratingppl END)= MaxValue

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

WITH X AS 
 (
    SELECT UniCourses.courseid,UniCourses.uniname,UniCourses.rating
          ,UniCourses.ratingppl,UniCourses.approved,Courses.courseid
          ,Courses.coursename  
          ,ROW_NUMBER() OVER (PARTITION BY Courses.coursename
                              ORDER BY ISNULL((rating / NULLIF(ratingppl, 0)),0)
                               DESC) rnRating
    FROM UniCourses 
    INNER JOIN Courses 
    ON UniCourses.courseid = Courses.courseid  
    WHERE UniCourses.approved=1
 )
SELECT courseid,uniname,rating ,ratingppl
      ,approved,courseid ,coursename
FROM x
WHERE rnRating = 1

Upvotes: 2

Paul
Paul

Reputation: 120

You're trying to do your join on courseid when both howard and hampton have the same courseid.

Change howard courseid to a different value and it'll return your unique record.

Upvotes: 0

Related Questions