This Is Your Brain On Informatics: MariaDB: Difference between revisions
No edit summary |
No edit summary |
||
| Line 69: | Line 69: | ||
<p>This data table was uploaded as a TSV (tab separated values) file.</p> | <p>This data table was uploaded as a TSV (tab separated values) file.</p> | ||
<p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE</p> | <p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE</p> | ||
===Answer to In-Class Question=== | |||
<p>Q: Find the maximums in column 2 of quintiles based on column 1 </p> | |||
<p> | |||
The following will create a temporary table that will order [var1] and also create a column with an counter variable named "id". | |||
<br /><br /> | |||
A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1]; | |||
<br /><br /> | |||
Then the use of the next command will need to be repeated for each quintile to find the maximum for each one. | |||
<br /><br /> | |||
A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile]; | |||
</p> | |||