Filter Last 3 Ratings Oracle sql

0

I'm making a report and I need to filter out the last 3 vendor evaluations in case this vendor has been downgraded by the date requested in the report.

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

Here's what I've 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;
    
asked by anonymous 08.02.2016 / 14:41

1 answer

1

As far as I understand, you want this subquery to return only the first 3. Then you have to resort to rownum. (If it 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)
    
24.10.2016 / 19:37