Cannot write sql query using RANK () function

Question

In my application, I need to get the best file from the database with the following rules:

• which has the highest number of priorities - priority 1
• which has the most comments - priority 2

If there are only files that have no comments and comments, then select only random.

My tables:

  CREATE TABLE "FILES" 
   (    "ID" NUMBER, 
    "OBJ_ID" NUMBER, 
    "NAME" VARCHAR2(30 BYTE)
   ) ;

  CREATE TABLE "UPVOTES" 
   (    "ID" NUMBER, 
    "TO_ID" NUMBER, 
    "TO_TYPE" NUMBER
   )  ;
   COMMENT ON COLUMN "UPVOTES"."TO_TYPE" IS '0 obj, 1 file, 2 comment';

  CREATE TABLE "COMMENTS" 
   (    "ID" NUMBER, 
    "OBJ_ID" NUMBER, 
    "CONTENT" VARCHAR2(20 BYTE), 
    "TO_TYPE" NUMBER, 
    "TO_ID" NUMBER
   ) ;

   COMMENT ON COLUMN "COMMENTS"."TO_TYPE" IS '0 object, 1 file';

Insert into FILES (ID,OBJ_ID,NAME) values ('1','1','best file for obj id = 1');
Insert into FILES (ID,OBJ_ID,NAME) values ('2','1','file obj1');
Insert into FILES (ID,OBJ_ID,NAME) values ('3','1','file obj1');
Insert into FILES (ID,OBJ_ID,NAME) values ('4','2','best file for obj id = 2');
Insert into FILES (ID,OBJ_ID,NAME) values ('5','2','file obj2');
Insert into FILES (ID,OBJ_ID,NAME) values ('6','3','only one file obj 3');
Insert into FILES (ID,OBJ_ID,NAME) values ('7','4','probilem file obj 4');

Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('1','1','1');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('2','1','1');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('3','7','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('4','2','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('5','2','0');
Insert into UPVOTES (ID,TO_ID,TO_TYPE) values ('6','2','0');

Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('1','1','comment 1','1','2');
Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('2','1','comment 2','1','2');
Insert into COMMENTS (ID,OBJ_ID,CONTENT,TO_TYPE,TO_ID) values ('3','2','comment 3','1','4');

My sql query:

SELECT obj_id, name FROM (
SELECT obj_id, name, rank, ROW_NUMBER() OVER (PARTITION BY obj_id ORDER BY rank) rownumb FROM (
SELECT f.obj_id, f.name, RANK() OVER (PARTITION BY f.obj_id ORDER BY COUNT(v.id) DESC, COUNT(DISTINCT com.id) DESC) rank
FROM files f
LEFT OUTER JOIN upvotes v
ON f.id = v.to_id
LEFT OUTER JOIN comments com
ON f.id = com.to_id
WHERE (v.to_type = 1 OR v.to_type IS NULL)
AND (com.to_type = 1 OR com.to_type IS NULL)
GROUP BY f.obj_id, f.name
)
)
WHERE rownumb = 1;

Expected Result:

obj_id    file name

1         best file for obj id = 1
2         best file for obj id = 2
3         only one file obj 3
4         probilem file obj 4

The problem is the line:

(v.to_type = 1 OR v.to_type IS NULL)

It does not work, because for objects (TO_TYPE = 0) there are objects with priority (TO_TYPE = 0) with the same TO_ID as the file identifier, but I still need to count upvotes for files (TO_TYPE = 1).

Can someone help me figure it out?

I am using Oracle Database 11g XE R2.

Answer 1

FROM files f
LEFT OUTER JOIN upvotes v
ON f.id = v.to_id
LEFT OUTER JOIN comments com
ON f.id = com.to_id
WHERE (v.to_type = 1 OR v.to_type IS NULL)
AND (com.to_type = 1 OR com.to_type IS NULL)

FROM files f
    LEFT OUTER JOIN upvotes v ON f.id = v.to_id AND v.to_type = 1
    LEFT OUTER JOIN comments com ON f.id = com.to_id AND com.to_type = 1