by Niklas Waller on December 30, 2010
in SQL
I have been working with pretty large pieces of SQL code and now as the code would grow bigger I realize the need of being able to use a global variable for the cases when I need to modify a string that is being used in many different pieces of code.
So for example if I need to do this comparison in several SQL code blocks:
left(a.createddate,10) > “2010-06-25”
and I use the same date in many code blocks, then it would be better if I could use a global variable that I could change in one place only.
This is easily accomplished. Define the global variable and execute:
Set @startDate = "2010-06-25";
Then use it like this:
left(a.createddate,10) > @startDate
Tagged as:
global,
mysql,
sql,
variable
by Niklas Waller on October 21, 2010
in SQL
Let’s say we have a resultset of an SQL query which consists of a bunch of rows and a couple of columns where we need to group rows on one column and get the most recent date of the grouped rows. We also need to get the value of the most recent date when adding 12 months. How is this accomplished with one SELECT statement?
| SELECT id, color, date FROM table; |
The above select-statement produces this resultset:
| id |
color |
date |
| 1 |
blue |
2008-01-01 |
| 2 |
blue |
2009-04-03 |
| 3 |
blue |
2010-06-23 |
| 4 |
red |
2009-10-22 |
| 5 |
red |
2008-10-03 |
| 6 |
green |
2007-02-23 |
| 7 |
green |
2010-01-20 |
| 8 |
yellow |
2007-11-29 |
We wish for this resultset:
| id |
color |
date |
newDate |
| 1 |
blue |
2010-06-23 |
2011-06-23 |
| 4 |
red |
2009-10-22 |
2010-10-22 |
| 7 |
green |
2010-01-20 |
2011-01-20 |
| 8 |
yellow |
2007-11-29 |
2008-11-29 |
The select case that produces this:
SELECT id, color, MAX(date) as date, DATE_ADD(MAX(date), INTERVAL 12 MONTH) as newDate
FROM table
group by color; |
Tagged as:
date,
date_add,
max,
sql
