SQL Partition by with conditions

Question

I want to partition the data on the basis of two columns Type and Env and fetch the top 5 records for each partition order by count desc. The problem that I'm facing is that I need to partition the Env on the basis of LIKE condition. Data -

Type	Environment	Count
T1	E1	1
T1	M1	2
T1	AB1	3
T2	E1	1
T2	M1	2
T2	CB1	3
T2	M1	5

The result that I want - Let's say I'm fetching top (1) record for now

Type	Environment	Count
T1	M1	2
T1	AB1	3
T2	CB1	3
T2	M1	5

Here I'm dividing the env on condition (env LIKE "%M%" and env NOT LIKE "%M")

One approach that I can think of is using partition and union but this is a very expensive call due to the large amount of data that I'm filtering from. Is there a better way to achieve this?

SELECT 
  *
FROM 
  (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Count DESC) AS maxCount
    FROM
      table 
    WHERE
      Env LIKE '%M%'
  ) AS t1
WHERE
  t1.maxCount <= 5
UNION
SELECT 
  *
FROM 
  (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Count DESC) AS maxCount
    FROM
      table 
    WHERE
      Env NOT LIKE '%M%'
  ) AS t1
WHERE
  t1.maxCount <= 5

One possible improvement is the LIKE/NOT LIKE in the where clause: If search string is simply 'M%' it improves performance. — Knut Boehnert, Aug 31 '21 at 10:07
@mseth . . . You should put your query in the question as *text*. Then someone who wants to help can modify it when answering your question. I also cannot tell what you want to accomplish. Are you looking for 10 rows or 10 rows per type? — Gordon Linoff, Aug 31 '21 at 11:07
@gordon I'm looking for 10 rows per type for each environment where I want to differentiate the env into 2 categories - one is with env LIKE "M%" and the other is env NOT LIKE 'M%'. So I should have 20 rows for each type ( 10 for "M%" and 10 for others). — mseth, Aug 31 '21 at 11:26

score 3 · Answer 1 · answered Aug 31 '21 at 11:11

You would seem to want an additional partition by in your row_number():

select t.*
from (select t.*,
             row_number() over (partition by type, case when environment like '%M%' then 1 else 2 end)
                                order by count desc
                               ) as seqnum
      from t
     ) t
where seqnum <= 5;

SQL Partition by with conditions

1 Answers1