CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 4234

Count SQL results form first query in second query in single statement

Here's my SQL statement:

(select * from items
where items.created > curdate() - interval 2 week 
order by items.created desc limit 0,10000000000)

union all

(select * from items
where items.created < curdate() - interval 2 week 
order by items.popularity desc limit 0,15)

I'm trying to figure out a way to limit the entire result of the query to a certain number (say 25). As it is now, this result returns an unlimited number for the first result (which is what I want), then returns 15 for the second result. I want to be able to limit the whole query so that even if the first result returns 8, the second result returns 17, total 25.

I believe to do this, I have to use count() somehow in the first query, then subtract that from the total I want and use that number as the 2nd query's limit. I have no idea how this is done.

Thanks in advance!

Upvotes: 0

Views: 663

Answers (4)

netcoder
netcoder

Reputation: 67705

No need for nested queries, simply do:

(select * from items
where items.created > curdate() - interval 2 week 
order by items.created desc)                      # remove LIMIT here

UNION ALL

(select * from items
where items.created < curdate() - interval 2 week 
order by items.popularity desc)                   # remove LIMIT here

LIMIT 25;                                         # add LIMIT here

This'll return the 25 first results from the first SELECT if there's at least 25. Otherwise it will fill up the remaining results with the second SELECT results until the limit of 25 is reached.

Upvotes: 1

dkretz
dkretz

Reputation: 37655

SELECT 1 AS sortkey, * from items ....
UNION ALL
SELECT 2 AS sortkey, * from items ....

ORDER BY sortkey, etc.
LIMIT 25

Upvotes: 0

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

Here is the required query - 

select *
from 
((select * from items
where items.created > curdate() - interval 2 week 
order by items.created desc limit 0,10000000000)

union all

(select * from items
where items.created < curdate() - interval 2 week 
order by items.popularity desc)) t
limit 0,25

Upvotes: 2

Yaakov Shoham
Yaakov Shoham

Reputation: 10548

Another select:

select * from
(

  (select * from items
  where items.created > curdate() - interval 2 week 
  order by items.created desc limit 0,10000000000)

  union all

  (select * from items
  where items.created < curdate() - interval 2 week 
  order by items.popularity desc)

) uniond_tables_alias
limit 25

The uniond_tables_alias is an alias for the uniond section, you can choose any name you want.

Upvotes: 1

Related Questions