title: Filter based on size in Hive
body: <p>I want to filter the records in hive based on size,how can this be achieved?
  Please help.</p>
  
  <p>Query</p>
  
  <pre><code>SELECT 
      t1.member_id,
      t2.first_name,
      t2.date_of_birth_sk,
      COLLECT_LIST(t3.measure_title) as all_measure_desc,
      size( COLLECT_LIST(t3.measure_title)) as ps
  FROM qms_gic_lifecycle t1 
  INNER JOIN dim_member t2 
  on t1.member_id = t2.member_id
  INNER JOIN dim_quality_measure t3 
  on t1.quality_measure_id = t3.quality_measure_id
  where t1.status &lt;&gt; 'closed'
  
  GROUP BY  
      t1.member_id,
      t2.first_name,
      t2.date_of_birth_sk;
  </code></pre>