Tag Archives: mysql

Translate query from mysql to oracle

IF Mysql: select if(field1=‘ok’,1,0) AS ‘DR‘ Oracle: select case when field1=‘ok’ then 1 else 0 end AS DR Date in condition Mysql: where creation_date>=’2014-06-01‘ Oracle: where trunc(creation_date)>= trunc(to_date(‘2014-06-01’, ‘rrrr-mm-dd’)) In Oracle, we should use trunc besides to_date to compare. For example trunc(sysdate+1). CONCAT Mysql: concat(field1, field2, field3) Oracle: concat(field1, concat(field2, field3)) or field1||field2||field3 GROUP_CONCAT Mysql: select GROUP_CONCAT(field1 SEPARATOR ‘,’) Oracle:… Read More »

Select distinct max in sql

Solution: SELECT t3.`id`, max_table3.max_type FROM table3 t3 JOIN (SELECT id, MAX(`type`) max_type FROM table3 GROUP BY id) max_table3 ON max_table3.max_type = t3.`type` AND max_table3.id = t3.`id` reference: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql

Mysql Million rows test: insertion

These days, I started to use mysql to query table with million rows. In order to get to know mysql performance better. I did this test. Basically, I created different tables with different type/number of indices. I tried to insert as many rows as possible within a certain time. And see the result. Environment: Windows… Read More »

Mysql Million rows test: select, join

These days, I started to use mysql to query table with million rows. In order to get to know mysql performance better. I did this test. Basically, I have 2 big tables and 1 small table. Each big table has 100M items. The small table has 1M items. I tested select, join operations among them… Read More »