-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.

Improve this question
3
  • 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. Commented Nov 3, 2021 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 Commented Nov 3, 2021 at 23:44
  • Wrap your query in a sub-query and then use any of the linked duplicates. Commented Nov 4, 2021 at 8:47

1 Answer 1

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.