You can use that table to generate any sequence of things. mysql> SELECT DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL i MONTH), INTERVAL DAYOFMONTH(CURRENT_DATE) -1 DAY) AS Date FROM integers; +------------+ | Date | +------------+ | 2008-04-01 | | 2008-03-01 | | 2008-02-01 | | 2008-01-01 | | 2007-12-01 | | 2007-11-01 | | 2007-10-01 | | 2007-09-01 | | 2007-08-01 | | 2007-07-01 | +------------+
On Mon, Apr 7, 2008 at 6:04 AM, Natalie Warshager <[EMAIL PROTECTED]> wrote: > Thank you, > However my original problem is to get 10 last month: > > SELECT > (DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -9 month))'Date' UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -8 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -7 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -6 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -5 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -4 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -3 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -2 month))UNION ALL > SELECT(DATE_ADD( > STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d'), INTERVAL -1 month))UNION ALL > SELECT(STR_TO_DATE(CONCAT(year(curdate()),'/',month(curdate()),'/1'), > '%Y/%m/%d')) > > > ************** In one query (as row)*************** > > Anyway it's look like I will have to live it that way > > Thanks for your offer > Natalie. > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Baron Schwartz > Sent: Monday, April 07, 2008 2:24 AM > To: Natalie Warshager > Cc: mysql@lists.mysql.com > Subject: Re: Can some one help me write it shorter? > > Hi, > > On Thu, Apr 3, 2008 at 3:25 AM, nataliew > <[EMAIL PROTECTED]> wrote: > > > > I need querylike this that make a row of numbers (in one execute) > > > > SELECT a from ( > > SELECT -1 a UNION ALL > > SELECT -2 UNION ALL > > SELECT -3 UNION ALL > > SELECT -4 UNION ALL > > SELECT -5 UNION ALL > > SELECT -6 UNION ALL > > SELECT -7 UNION ALL > > SELECT -8 UNION ALL > > SELECT -9) t; > > That's the best way to write it if you don't want to refer to any > tables. However, I like to have a table handy to generate such > sequences of numbers: > > http://www.xaprb.com/blog/2005/12/07/the-integers-table/ > > Regards > Baron > > -- > > Baron Schwartz, Senior Consultant, Percona Inc. > Tel: +1 888 401 3401 ext 507 > 24/7 Emergency Line +1 888 401 3401 ext 911 > Our Services: http://www.percona.com/services.html > Our Blog: http://www.mysqlperformanceblog.com/ > -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
