 | Start a set with this search |
 | Include this search in one of my sets |
 | Exclude this search from one of my sets |
 | Permalink to these results Paste this link in email or IM: |
 | Atom feed for tracking future search results Paste this URL into your reader: |
4 messages in com.mysql.lists.mysqlRe: Can some one help me write it sho...| From | Sent On | Attachments |
|---|---|---|
| nataliew | Apr 3, 2008 12:25 am | |
| Baron Schwartz | Apr 6, 2008 6:23 pm | |
| Moon's Father | Apr 6, 2008 6:29 pm | |
| Baron Schwartz | Apr 7, 2008 4:51 am |
| Permalink for this message Paste this link in email or IM: |
| Permalink for this thread Paste this link in email or IM: |
| Atom feed for this thread Paste this URL into your reader: |
| Subject: | Re: Can some one help me write it shorter? | Actions |
|---|---|---|
| From: | Baron Schwartz (bar...@percona.com) | |
| Date: | Apr 7, 2008 4:51:35 am | |
| List: | com.mysql.lists.mysql | |
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 <Nata...@mediamarket.ie> 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: baro...@gmail.com [mailto:baro...@gmail.com] On Behalf Of Baron Schwartz Sent: Monday, April 07, 2008 2:24 AM To: Natalie Warshager Cc: mys...@lists.mysql.com Subject: Re: Can some one help me write it shorter?
Hi,
On Thu, Apr 3, 2008 at 3:25 AM, nataliew <nata...@mediamarket.ie> 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/