-1

Running this query it will create a table with 3 columns (place name, id of vaccines and the count of how many the vaccines were applied to different persons).

select vaccines.placename, vaccinetype.idvaccine,count(*)
from  vaccines,request,vaccinetype
where request.idvaccine = vaccines.idvaccine
and vaccinetype.idvaccine = request.idvaccine
group by vaccines.placename,vaccinetype.idvaccine
order by vaccines.placename, vaccinetype.idvaccine

Image: Query Result

In the image of the query result above, you will see that the same vaccine id was applied in different places but this is something that i want to filter, i want to only show those vaccines id where was the most applied to the persons. For example, in this table we would have to eliminate row 6 because row 1 already exists with the same vaccine code and also that in the column count (*) the value is higher than row 6.

I have tried to do a sub query but it didn't filter correctly.

MT0
  • 143,790
  • 11
  • 59
  • 117
jawionfan
  • 1
  • You want to eliminate row 6 because row 1 has an higher COUNT for the same IDVACCINE, but what about row 4? Shouldn't you also eliminate that one? If not, then it's unclear what you're trying to accomplish by treating row 4 and 6 differently. – Marco Bonelli Nov 03 '21 at 23:28
  • Yes you are right @MarcoBonelli, i didn't finish writing the example. Also it should be deleted the row 7 and 4 – jawionfan Nov 03 '21 at 23:44
  • Wrap your query in a sub-query and then use any of the linked duplicates. – MT0 Nov 04 '21 at 08:47

1 Answers1

0

Here's one option: rank rows per vaccineid, sorted by count result, and then fetch rows that rank as "highest". Read comments within code.

WITH
   your_query
   AS
   -- this is your query, slightly rewritten so that it uses
   -- * JOINs, so that you actually join tables there and leave WHERE clause for
   --   conditions (if any; there are none in your query)
   -- * table aliases, which make it easier to read
   -- * column alias applied to COUNT function
      (  SELECT v.placename, t.idvaccine, COUNT (*) cnt
           FROM vaccines v
                JOIN request r ON r.idvaccine = v.idvaccine
                JOIN vaccinetype t ON t.idvaccine = r.idvaccine
       GROUP BY v.placename, t.idvaccine),
   temp
   AS
   -- it fetches values from your query, with addition of the RANK analytic function
   -- which ranks rows per each IDVACCINE, sorted by COUNT result in descending order
      (SELECT placename,
              idvaccine,
              cnt,
              RANK () OVER (PARTITION BY idvaccine ORDER BY cnt DESC) rnk
         FROM your_query)
-- finally, retrieve rows that rank as "highest" in TEMP CTE         
  SELECT placename, idvaccine, cnt
    FROM temp
   WHERE rnk = 1
ORDER BY placename, idvaccine;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57