Query to select limit in specific condition-CodePudding

In my table, I have the 'email' column. I want to select 1000 data where max 100 emails are 'gmail'. Is there any way to write one query to get data?

What I am doing is, to create two queries and then merge them.

SELECT email from my_table where email not like '%@gmail.%' limit 900;
SELECT email from my_table where email like '%@gmail.%' limit 100;

CodePudding user response：

A simple union all should be it. However, to make sure that you're getting exactly 1000 rows (in case there are more than 1000 rows but less than 100 are @gmail) you can do this:

with u as 
(SELECT email from my_table where email like '%@gmail.%' limit 100)
select * from u
union all
(SELECT email from my_table 
where email not like '%@gmail.%' 
limit 1000 - (select count(*) from u));

CodePudding user response：

probably there could be better answer but that one fix your problem:

select email
  from (
        select email,
               row_number() over(partition by 1) as rn
          from my_table mt
         where email not like '%@gmail.%'
       )q1
 where rn <= 900
 union all 
select email
  from (
        select email,
               row_number() over(partition by 1) as rn
          from my_table mt
         where email like '%@gmail.%'
       )q1
 where rn <= 100

CodePudding user response：

You could use cte functions to write this as a single query:

WITH 
query_1 AS 
(SELECT email from my_table where email not like '%@gmail.%' limit 900),
query_2 AS 
(SELECT email from my_table where email like '%@gmail.%' limit 100)

SELECT email FROM query_1 
UNION ALL
SELECT email FROM query_2;