3
If you are using a older MySQL versions like 5.1 - 5.7 , you need to use MySQL user variables as these MySQL versions does not support window functions and or common table expressions ..
I ported the answers of @Akina and @mustaccio and the fiddle of Akina
to a MySQL 5.1+ working query.
Warning as MySQL 5.1 to MySQL 5.7 does not use window functions and ...
3
It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column:
ALTER TABLE
MY_TABLE
ADD COLUMN
custom_column VARCHAR(50) AS
(
SUBSTRING(column2, 4)
)
;
This ...
2
You can use exists and not exists
select ...
from employee e
where exists (
select * from dependent where ssn = e.ssn and relationship = 'Son'
)
and not exists (
select * from dependent where ssn = e.ssn and relationship = 'Daughter'
)
You can also achieve this with a left join
select ...
from employee e
left join dependent d
on e....
2
SELECT name, COUNT(item_id)
FROM ( SELECT c.name, i.item_id
FROM categories c
LEFT JOIN item_category ic ON ic.category_id = c.category_id
LEFT JOIN items i ON ic.item_id = i.item_id
UNION ALL
SELECT c.name, i.item_id
FROM categories c
LEFT JOIN subcategories sc ON sc.category_id = c.category_id
LEFT ...
2
Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import:
LOAD DATA INFILE
....
(field1, field2, ..., @date, ...)
SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');
2
the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows
Test:
WITH cte AS ( SELECT price, SUM(volume) OVER (ORDER BY price ASC) summ
FROM sourcetable )
SELECT AVG(price)
FROM cte
WHERE summ <= 0.1 * ( SELECT MAX(summ)
FROM cte )
fiddle
2
Depending on the size of the table, this variant of Akina's solution might perform a little better by calculating both running total and full total of volume in one go:
WITH cte AS (
SELECT
price,
SUM(volume) OVER (ORDER BY price ASC) running,
SUM(volume) OVER (ORDER BY price ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND ...
1
Direct solution:
(0[1-9])|([1-9][0-9])
It is simple enough for do not look for shortening or another optimization, I think.
1
Schematically.
CREATE TABLE users ( id PRIMARY KEY, ... );
CREATE TABLE groups ( leader_id REFERENCES users (id),
member_id REFERENCES users (id) );
The keys (including primary) and constraints in groups are dependent by:
does the user may be a member of one group only, or of a lot of groups;
does the group leader may be a member of ...
1
You can create timestamp in SYSTEM timezone with:
FROM_UNIXTIME(timestamp_in_seconds) -- This is a number, not a datetime
To convert back to a timestamp you can use UNIX_TIMESTAMP function.
This sample demonstrate how to generate random datetime based on starting point. It give you only correct timestamps:
select FROM_UNIXTIME(UNIX_TIMESTAMP('2016-03-27 ...
1
According to Documentation at MySQL 5.7 Doc, you will only have to add '--skip-ssl' to your mysqld startup command. This will completely disable SSL support on server side.
1
In my situation slave replication coordinates were broken and I fixed this by resetting my slave and connect my slave to master by new coordinates without any data loss.
First, check if your slave gtid_slave_pos is differing from other slaves or master gtid_current_pos:
# on master
SELECT @@GLOBAL.gtid_current_pos;
+---------------------------+ ...
1
How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0:
SELECT t1.contact_id,
t1.contact_group_id,
t1.contact_email,
COUNT(t2.contact_id) - 1 row_number
FROM contacts t1
JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id
AND t1.contact_id >= t2....
1
You don't need all binary logs from the master. Just the ones that your slave hasn't read yet.
On your slave you can do show slave status\G. Look for the lines Master_Log_File and Relay_Master_Log_File. The first is the binary log file, that your master is using, the second is the log file that your slave is currently reading.
You can then delete all ...
1
The Slave purges old log files on its own. No problem.
The Master does not have a perfect way to get rid of old binlogs.
The Master can't remove an old binlog until all the slaves have copied it to their relay logs. But the Master neither has a list of Slaves, nor does it know where each is in the copying process.
expire_logs_days works pretty well, but ...
1
You want
AND d.tentative_date_of_return <= CURDATE() + INTERVAL 3 DAY
1
what is wrong with this code?
Wrong point is:
WHERE ... Relationship='Son' AND NOT Relationship='Daughter'
The conditions are applied to THE SAME RECORD. So when Relationship is equal to 'Son' then it is not equal 'Daughter' in this record automatically.
The conditions do not check any another record. So if a record where Relationship='Son' exists then ...
1
As already answered in comments by @Madhur (All the credits goes to him).
You can use having clause to use alias name without group by.
So instead of WHERE it should be HAVING.
SELECT
column1, column2, SUBSTRING(column2, 4) AS custom_column
FROM
MY_TABLE
HAVING custom_column = 'xyz'
Note: To know the detail reason why alias name can't be ...
Only top voted, non community-wiki answers of a minimum length are eligible
