Highest number of color by each type

1

greetings,

given the following data

declare @cats table(type char(10), color char(1))

insert into @cats
select 'abyssinian', 'B' union all
select 'abyssinian', 'B' union all
select 'abyssinian', 'B' union all
select 'abyssinian', 'B' union all
select 'abyssinian', 'A' union all
select 'abyssinian', 'C' union all
select 'Dog', 'C' union all
select 'Dog', 'A' union all
select 'Dog', 'A' union all
select 'Dog', 'A' union all
select 'Dog', 'A' union all
select 'Dog', 'A' union all
select 'Dog', 'B' union all
select 'Dog', 'C'

how can I get the top ranking value by type please?

  1. 'abyssinian', 'B'
  2. 'Dog', 'A'
2

this does it but feels verbose!

;with src
as
(

SELECT
  RANK () OVER ( 
			PARTITION BY type
			ORDER BY points DESC
		) price_rank,
  type,
  color,
  points
FROM (select count(color) points, color, type 
	from @cats group by type, color) src
)
select *
 from src
 where price_rank = 1
3

Hi

hope this helps

SELECT 
    top 1 with ties
      type
     , color  
FROM 
   @cats
GROUP BY
    type 
    , color 
ORDER BY
     row_number() over (partition by type order by count(color) desc)

image

1 Like
4

Thanks @harishgg1 that does the trick!