Database

Simple way to calculate median with MySQL

In MariaDB / MySQL: Steve Cohen points out, that after the first pass, @rownum will contain the total number of rows. This can be used to determine the median, so no second pass or join is needed. Also AVG(dd.val) and dd.row_number IN(…) is used to correctly produce a median when there are an even number of records. Reasoning: Finally, MariaDB 10.3.3+ … Read more

Mysql command not found in OS X 10.7

This is the problem with your $PATH: /usr/local//usr/local/mysql/bin/private/var/mysql/private/var/mysql/bin. $PATH is where the shell searches for command files. Folders to search in need to be separated with a colon. And so you want /usr/local/mysql/bin/ in your path but instead it searches in /usr/local//usr/local/mysql/bin/private/var/mysql/private/var/mysql/bin, which probably doesn’t exist. Instead you want ${PATH}:/usr/local/mysql/bin. So do export PATH=${PATH}:/usr/local/mysql/bin. If you want this to be run … Read more

SQL Server Insert if not exists

instead of below Code replace with Updated : (thanks to @Marc Durdin for pointing) Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a … Read more

mysql update multiple columns with same now()

Found a solution: I found this in MySQL Docs and after a few tests it works: the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL. UPDATE t1 SET col1 = col1 + 1, … Read more

mysqladmin: connect to server at ‘localhost’ failed

Short version: If your MySQL user root needs a password to connect, it might be a good idea to have mysqladmin provide that password 😉 Longer version: Your MySQL user root seems to need a password to connect setting the mysql root password new But mysqladmin tries to connect without a password ‘Access denied for user ‘root’@’localhost’ (using password: NO)’ And mysqladmin does that because you’re not … Read more

Difference between numeric, float and decimal in SQL Server

use the float or real data types only if the precision provided by decimal (up to 38 digits) is insufficient Approximate numeric data types(see table 3.3) do not store the exact values specified for many numbers; they store an extremely close approximation of the value. (Technet) Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to … Read more

DateTime2 vs DateTime in SQL Server

The MSDN documentation for datetime recommends using datetime2. Here is their recommendation: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications. datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified … Read more

+ More