The data set is like this: Table A ==type===|| ===score==== Two columns type ands score, and Table B ==== id ==== || ==== data ==== Two columns as well id and data. So, the details are as …
Tag: mysql
Replace conditional subquery in Order By (Performance consideration)
I have a ratings table (t2) I’m using to sort queries by lower_bound DESC. The problem is, the default value of lower_bound has to be null or 0. When a rating occurs, the row is updated with Wilson’s rating algo. This means that new entries get ordered before all other entries in a lower_bound DES…
MySQL: How to compare multiple field value of a row and get matched or lowest one?
I have a MySQL table (below): I can fetch the min or max of the column values simply but my problem is: If any 3 or more fields value matched then fetch that value (if 3 fields values matched more than one value then the lowest of those values), for row #1 2100 will be the output and for row#3
Sql: Find sum of column from second table using date from first table
I’ve been struggling to build a query that calculate the sum of column called ‘TIDAL_VOLUME’ with respect to date value that’s coming from another table. Please see the content of the Table_1: Please see the content of the Table_2: Note: TIDAL_VOLUME might have NULL as well. Now, the s…
UNION 2 tables lots fields, few different, possible use SELECT *?
I have 2 MYSQL tables, one with 794 fields, another with 796 fields, the common 794 fields the same. Yes genuine figures, importing data from old system to new one. If I do: SELECT foo FROM (SELECT …
SQL if not null update
I have this query UPDATE users SET username = Param1,email = Param2,PASSWORD = Param3 WHERE id = Param4; this is updating my user row, but i want to say: If email OR password is not null update them …
Search for many rows with many distinct values
I am looking to implement a cache in MySQL. I’m doing this because the app I’m developing needs a cache but has no persistence. I am keeping a collection of SHA-1 checksums for files. The user can …
Trouble understanding a LIKE operator syntax
I got pasted a source code to extract a particular dataset and I am having a problem understanding one of the bits. Here’s the part of a query: Could you help me translate what does the inside bracket of this LIKE operator mean? Many thanks in advance. Answer COALESCE takes second argument if first one …
MySQL TIMEDIFF Less than an hour, invalid time
I have a function in MySQL that will select the time someone clocked in, and select the time someone clocked out and link them together. It will calculate how many hours that person has been on the clock in total between two provided dates. I then created another function that selects the break events and sub…
MySQL syntax for summing several count data
I have a MySQL table called things_they_own with two columns: person and thing and my primary key is the combination of the two. Thus, there are several rows where the first column is a certain person with the thing column being different for each, and several rows where the second column is a certain thing a…