CODEWITHSUNDEEP
sqloracle-database
Utku Zihnioglu
Utku Zihnioglu

Reputation: 4883

Distinct select on Oracle

What i am trying to do is a simple recommender , must take the biggest weighted top 40 element's node2 element. Calculation for weight comes from (E.WEIGHT * K.GRADE). Now this code succesfully returns top 40 elements. However, i don't want E.NODE2 to return duplicates. POSTGRE SQL allowed me to do SELECT DISTINCT ON (NODE2) E.NODE2 , (E.WEIGHT * K.GRADE). How can i do the same in oracle?

The complete sql query;

SELECT *
 FROM   (SELECT DISTINCT E.NODE2  , (E.WEIGHT * K.GRADE)
    FROM KUAISFAST K, EDGES E
    WHERE K.ID = 1 AND K.COURSE_ID = E.NODE1 AND E.NODE2 NOT IN(
        SELECT K2.COURSE_ID
        FROM KUAISFAST K2
        WHERE K2.ID = 1
        ) 
    ORDER BY( E.WEIGHT * K.GRADE ) DESC) TEMP
WHERE rownum <= 40

Upvotes: 0

Views: 609

Answers (3)

user552869
user552869

Reputation: 26

This should solve your problem, altough quite slow

SELECT * FROM
(SELECT *
 FROM   (SELECT E.NODE2 ,  max(E.WEIGHT * K.GRADE ) AS MAXDE
    FROM KUAISFAST K, EDGES E
    WHERE K.ID = 1 AND K.COURSE_ID = E.NODE1 AND E.NODE2 NOT IN(
        SELECT K2.COURSE_ID
        FROM KUAISFAST K2
        WHERE K2.ID = 1
        ) 
        GROUP BY E.NODE2 )
ORDER BY MAXDE DESC)
WHERE rownum <= 40

Upvotes: 1

WW.
WW.

Reputation: 24311

In your subselect, I think you want: MAX(E.WEIGTH * K.GRADE) so that only one value comes back for each E.NODE2.

This means you'll need to GROUP BY E.NODE2 as well.

Upvotes: 1

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40613

I believe you want something like

SELECT *
FROM   (
  SELECT 
      E.NODE2,
     (E.WEIGHT * K.GRADE),
      ROW_NUMBER() OVER (PARTITION BY E.NODE2 ORDER BY E.WEIGHT * K.GRADE DESC) R
  FROM 
      KUAISFAST K,
      EDGES E
  WHERE 
      K.ID = 1 AND 
      K.COURSE_ID = E.NODE1 AND 
      E.NODE2 NOT IN
        (  SELECT K2.COURSE_ID
           FROM KUAISFAST K2
          WHERE K2.ID = 1
        ) 
    ORDER BY (E.WEIGHT * K.GRADE) DESC
) TEMP
WHERE R=1 AND 
ROWNUM <= 40

Upvotes: 1

Related Questions