I am looking for a query that selects a record from each group that meets a user-defined criterion. I can explain with the following illustrative table: A simple GROUP BY categoryID query returns the first record in each group, as shown below: To return the last record in each group we can use the approach su…
Tag: mariadb
Mysql Conditional Aggregation Using group_by query and Case to group Month and Year
I’ve a table structure where I’m saving the data in the below format. The ledger amount needs to be divided into two columns and grouped through the sum equation. Those rows having receipt_id not as null their ledger_amount needs to be displayed as sum(ledger_amount) as credit whole rows having pa…
how to validate date null in mysql?
I’m working with MariaDB. when I try to show some result don’t show me nothing. select if(fecha is null,’hello’,’bye’) dat from table I’m trying to show me some result, but I don’t know how?…
MariaDB/MySQL UPDATE statement with multiple joins including a ranged join
I have for tables A Login History An IP To Location Table An Account Table Some Orders All tables have fitting indices for this problem. My goal is to fill the country code of the orders with the country from the ip2location table. I have a login history and because I want to make the problem not more complic…
MariaDb InnoDB deadlock while doing many inserts
I’m having deadlock issues with a MariaDB (10.0.27)if it’s under pressure. The database schema is basically a hierarchy and the leaf node causes this: This is how my leaf table looks like: I’m able to reproduce the deadlock with an input set that puts the system under pressure. With this, I&…
SQL order by a function with literal values
I want to order some SQL result using the result of a calculation. I can select this SELECT sqrt( POWER(TERM_0 – -0.12985125184059143,2)) from faces f limit 3 So the syntax seems correct, but when I …
Update column value based on ORDER of another query
What I have: Hi guys, I have two tables. #1 model Where I store all models #2 video_models_model which connect model and video table (video is unrelated now) until now always when I needed models I ordered them based on in how many videos they are used in What I need: However I found out that sometimes I need…
Mysql IF statement Error on Nested if sentence
I am trying to create a procedure to update the stopTime field on my works table. The query I am using is: update mydb.works set stopTime= (IF((SELECT table_name FROM information_schema.tables …
How can I do math operations in query SELECT?
I have a table of statistics where operations with water are counted. I have three types of water 0,2,3. But there is water type 0, which includes type 2, type 3 and type 0, that is, water type 0 = 0 + 2 + 3. That is, if an operation with a water type for example 2, then in the
MariaDB SUM returns wrong value on column type float
version: 5.5.53-MariaDB I added this table to my MariaDB: CREATE TABLE `myTable` ( `key` int(10) unsigned NOT NULL, `val_float` float unsigned NOT NULL, `val_bigint` bigint(20) unsigned NOT …