A Day in Paradise

MySQL date and time commands annoy me almost as much as the MySQL online manual. I’m moving my office so my MySQL reference manual was in some box… and I had to resort to digging through the manual.
I don’t know why I always have a hard time remembering
SELECT DATE_SUB( NOW( ) , INTERVAL 7 […]

For some reason, I had a hard time connecting to an external server through my command line client. I’ve done it before, but I had to dig up the manual to find the answer…
mysql -uusername -ppassword -hhostname
I’m sure that’s what I was doing but it wasn’t working… funny thing. Anyway, I’m posting this so […]

An client wanted to add an arbitrary ordering to a set of rows which were being displayed in some legacy code, I had this functionality in newer versions of the software that he was running but due to “technical issues” couldn’t upgrade the software at the time.
The correct way to do this would obviously […]

I have a log and I want to list the last time an entry was made by each of the users
SELECT user_id, MAX( log_date ) AS date,
DATE_FORMAT( MAX( log_date ) , “%M, %Y” ) AS english_date
FROM log_table
GROUP BY user_id
I’m not sure if this is […]

SQL to Select a random row from a database table
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Want to find users whose lastnames start with the letters between A and O?
SELECT * FROM users
WHERE SUBSTR(lastname, 1,1)
BETWEEN ‘A’ AND ‘O’;
Notice SUBSTR is 1 based… annoying!
Second observation, SUBSTR is a 4.1 alias for SUBSTRING so if you aren’t using 4.1+ you’ll want to use SUBSTRING rather than SUBSTR