CODEWITHSUNDEEP
sqloracle-database
Josh
Josh

Reputation: 9

Why isn't Oracle's Order By Working Properly?

So basically I'm running this SQL oracle program and I want to print 5 rows and have them ordered based on average rating. So far I have 2 issues: it keeps printing duplicate values and it's not ordered by rating.

The tables are as follows:

Title_basics
----------------------
Primarytitle NVARCHAR2(950)
Genres NVARCHAR2(350)
Endyear VARCHAR2(4)


Title_ratings
--------------------------------------
Averagerating Number(5,2)

COLUMN Title FORMAT a30 HEADING Title
COLUMN Genres FORMAT a20 HEADING Genres
COLUMN Rating FORMAT 99.99 HEADING Rating
COLUMN Year FORMAT a7 HEADING Format

SELECT *
FROM ( SELECT DISTINCT tb.primarytitle AS Title, tb.genres AS Genres, tr.averagerating AS Rating, tb.endyear AS Year
                FROM title_basics tb, title_ratings tr
                WHERE tb.genres LIKE '%Comedy%' AND (tb.endyear = '2001' OR tb.endyear = '2002' OR tb.endyear = '2003' OR tb.endyear = '2004' OR tb.endyear = '2005')
                ORDER BY tb.endyear DESC
)
WHERE ROWNUM <= 5;

Upvotes: 0

Views: 153

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your question really makes no sense. You have no join columns in the average ratings table. Presumably, there is a column that ties it back to the other table:

SELECT *
FROM (SELECT tb.primarytitle AS Title, tb.genres AS Genres, tr.averagerating AS Rating, tb.endyear AS Year
      FROM title_basics tb JOIN
           title_ratings tr
           ON tb.? = tr.?   -- presumably some JOIN condition here
      WHERE tb.genres LIKE '%Comedy%' AND 
            tb.endyear IN ('2001', '2002', '2003', '2004', '2005')
      ORDER BY tr.averagerating DESC
     ) br
WHERE ROWNUM <= 5;

The ? is for the columns that specify the JOIN conditions.

If you join the tables correctly, then you probably don't need SELECT DISTINCT.

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18630

You can use the FETCH clause to specify the number of rows to return. It is a lot more flexible than ROWNUM (docs)

SELECT DISTINCT tb.primarytitle     AS title,
                tb.genres           AS genres,
                tr.averagerating    AS rating,
                tb.endyear          AS year
  FROM title_basics   tb,
       title_ratings  tr
 WHERE tb.genres LIKE '%Comedy%' AND
       ( tb.endyear = '2001' OR
         tb.endyear = '2002' OR
         tb.endyear = '2003' OR
         tb.endyear = '2004' OR
         tb.endyear = '2005' )
 ORDER BY tb.averagerating DESC
FETCH FIRST 5 ROWS ONLY;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Put order by clause outside of your subquery -

SELECT *
FROM ( SELECT DISTINCT tb.primarytitle AS Title, tb.genres AS Genres, tr.averagerating AS Rating, tb.endyear AS Year
                FROM title_basics tb, title_ratings tr
                WHERE tb.genres LIKE '%Comedy%' AND (tb.endyear = '2001' OR tb.endyear = '2002' OR tb.endyear = '2003' OR tb.endyear = '2004' OR tb.endyear = '2005')
                
)
WHERE ROWNUM <= 5
ORDER BY Year DESC

Upvotes: 1

Related Questions