Filter last 3 ratings Oracle sql

Asked

Viewed 131 times

0

I am making a report and need to filter the 3 latest supplier evaluations in case that supplier is with the negative review on the date requested in the report.

Each evaluation has several questions and need to bring all these questions from each of the last 3 reviews from each supplier.

Follow what I got ready:

 SELECT   forn.cd_fornecedor,
     forn.nm_fornecedor,
     isoa.cd_avaliacao,
     isoa.cd_ranking,
     isoa.vl_nota,
     isoa.vl_media,
     isoa.dt_avaliacao,
     isor.cd_resposta,
     isor.cd_itpergunta,
     isoi.cd_pergunta,
     isoi.ds_itpergunta,
     isop.ds_pergunta

   FROM fornecedor forn,
        iso_avaliacao isoa,
        iso_resposta isor,
        iso_itpergunta isoi,
        iso_pergunta isop

  WHERE forn.cd_fornecedor = isoa.cd_fornecedor
    AND isoa.cd_avaliacao = isor.cd_avaliacao
    AND isor.cd_itpergunta = isoi.cd_itpergunta
    AND isoi.cd_pergunta = isop.cd_pergunta
    --AND isoa.cd_ranking IN (1, 7)
    AND forn.cd_fornecedor in (select isoa2.cd_fornecedor 
                                from iso_avaliacao isoa2
                                where isoa2.dt_avaliacao between to_date('01/01/2016') and to_date('20/01/2016')
                                        and isoa2.cd_ranking in (1,7)
                                        /*and isoa2.cd_avaliacao in (select isoa3.cd_avaliacao
                                                                        from iso_avaliacao isoa3
                                                                        where )*/
                                        )

 --AND FORN.CD_FORNECEDOR = 4272 
 --AND ISOP.CD_PERGUNTA > 6 
 --AND ISOI.DS_ITPERGUNTA = 'NAO' 

ORDER BY forn.nm_fornecedor, isoa.cd_avaliacao desc,   isoa.dt_avaliacao;
oracle pl-sql

  • Two solutions occur to me , using a subquery with order by and testing runnum , something like this : select * from (select * from table order by data desc) Where rownum < 4 or use Analytic Functions from Rrcle ( http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174 ) using RANK or DENSE_RANK functions

1 answer

1

From what I understand, you want this subquerie to return only the first three. Then you must turn to rownum. (If you were Oracle12c, there is now a more effective way). ex:

(select * from 
     (select isoa2.cd_fornecedor 
       from iso_avaliacao isoa2
      where isoa2.dt_avaliacao between to_date('01/01/2016') and  to_date('20/01/2016')
          and isoa2.cd_ranking in (1,7))
where rownum<=3)

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.