asked
Looking for Count of Field1 where Field2 is in High, Medium, or Low. Field 1 can only be counted once
I need a count of how many are HIGH, MEDIUM, LOW based on the following logic Looking at the highlighted examples: L14815 counts against high but not medium L14495 counts against Medium but not low i.e. if a RECORD name is duplicated then the count only increments for the higher priority Each record name is only counted once and in its highest category only ![alt text][1] [1]: /storage/temp/3490-1.png
Pretty sparse on details here but I think something along these lines is what you are looking for. with SortedResults as ( select COUNT(*) as MyCount , RECORD_NAME , ASIT_PRIORITY , ROW_NUMBER()over(partition by RECORD_NAME order by case ASIT_PRIORITY when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 end) as RowNum FROM YourTable GROUP BY RECORD_NAME , ASIT_PRIORITY ) select * from SortedResults where RowNum = 1
18 People are following this question.
