multiple query into one?

Question

How do put this into one query?

For example:

SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2012' limit 20
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2013' limit 20
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2014' limit 20
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2015' limit 20

SELECT * FROM `result` WHERE status = 'new' AND DAY(`end_date`)  = '1' limit 20
SELECT * FROM `result` WHERE status = 'new' AND DAY(`end_date`)  = '2' limit 20
... and to 31

and same for the Month Jan to Dec

Basically show 20 records of each day, month and year.

32 days in a month :-)? The obvious answer is to use a lot of unions. — Ben
– Ben, Commented Aug 4, 2012 at 11:01
I think you could probably come up with views that select the top 20 records (but I can't help but notice you're not ordering...) and select from that. — user684934
– user684934, Commented Aug 4, 2012 at 11:03
MySQL doesn't have functionality like row_number(). This makes it extremely hard to get 20 records per group instead of just 20 records. This means that writing each query independantly (and possibly using UNION to combine the results) really is the most straight forward option. — MatBailie
– MatBailie, Commented Aug 4, 2012 at 11:05
are you missing order by clause ? or you are taking any 20 recordds? — Joe G Joseph
– Joe G Joseph, Commented Aug 4, 2012 at 11:07

aleroot · Accepted Answer · 2012-08-04 11:40:43Z

3

You can merge result set using UNION in this way :

SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2012' limit 20
UNION
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2013' limit 20
UNION
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2014' limit 20
UNION
SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  = '2015' limit 20
UNION
SELECT * FROM `result` WHERE status = 'new' AND DAY(`end_date`)  = '1' limit 20
UNION 
SELECT * FROM `result` WHERE status = 'new' AND DAY(`end_date`)  = '2' limit 20
ORDER BY submit_date DESC

answered Aug 4, 2012 at 11:40

aleroot

73k32 gold badges182 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I'll-Be-Back Over a year ago

Where would you include order by submit_date desc?

I'll-Be-Back Over a year ago

'UNION ALL' does not remove duplicate which I would prefer 'UNION'

Mahmoud Fayez · Accepted Answer · 2012-08-04 11:38:03Z

1

I think you need to use a stored function instead maybe something like that may help you

create function MyRecords()
RETURNS @MyResultsTable table
BEGIN
    select * into @MyResultsTable from ? where ?
    select * into @MyResultsTable from ? where ?
    select * into @MyResultsTable from ? where ?
    select * into @MyResultsTable from ? where ?
    .....
    .....
    .....
end

I do not have MySql installed now but you should try to create it from the console and call it. Good luck!

answered Aug 4, 2012 at 11:38

Mahmoud Fayez

3,4692 gold badges22 silver badges39 bronze badges

Comments

phant0m · Accepted Answer · 2012-08-04 11:18:58Z

-1

SELECT * FROM `result` WHERE status = 'new' AND YEAR(`end_date`)  >= '2012' AND YEAR(`end_date`) <= '2015' AND DAY(`end_date`)  >= '1' AND DAY(`end_date`)  <= '32' AND  MONTH(`end_date`)  >= '1' AND  MONTH(`end_date`)  <= '12'  limit 20

Does this do, what you want?

edited Aug 4, 2012 at 11:18

phant0m

17k6 gold badges52 silver badges84 bronze badges

answered Aug 4, 2012 at 11:06

Mattigins

1,01610 silver badges27 bronze badges

Collectives™ on Stack Overflow

multiple query into one?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related