0

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!

Improve this question
0

4 Answers 4

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

4 Comments

Sweet, this works (I'll accept once SO lets me). One small change you might want to make for future readers: I had to take out the limit 0,15 from the second select, because it was still limiting that one.
Oops, one more edit. I just discovered the order by for the second statement doesn't work without a limit, so I just put limit 0,10000000000 in both selects.
@Jake: Weird. I took some table with ids, and asked this query: select * from (SELECT * FROM tsite WHERE mod(id,2) = 1 limit 1 union SELECT * FROM tsite WHERE mod(id,2) = 0 order by id desc/asc) z limit 4 and I've got the correct records in the correct order.
I'm not sure what causes it, but you can see that in a previous question I asked relating to this same statement, the answer acknowledges the need for the limits: stackoverflow.com/questions/7301571/…
1

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.

Improve this answer

1 Comment

sometimes it will sometimes it won't. The only guaranteed ordering is what you explicitly put into an ORDER BY.
0 
SELECT 1 AS sortkey, * from items ....
UNION ALL
SELECT 2 AS sortkey, * from items ....

ORDER BY sortkey, etc.
LIMIT 25
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.