CODEWITHSUNDEEP
sqlt-sqlscopepartition-by
mseth
mseth

Reputation: 31

SQL Partition by with conditions

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

Upvotes: 1

Views: 4584

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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;

Upvotes: 3

Related Questions