2
I think the sentence is quite clear, using a left join to get null USER_ID's and returning those where count = 0;
SELECT
BOOK_ID, COUNT(USERS.USER_ID) AS USERS
FROM
BOOKS
LEFT JOIN
USERS
ON USERS.USER_ID = BOOKS.USER_ID
GROUP BY
BOOK_ID
HAVING COUNT(USERS.USER_ID) = 0;
BOOK_ID | USERS
------: | ----:
2 | 0
3 | 0
...
2
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 ...
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 ...
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
1
Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended.
Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if ...
1
Works for me in both MyISAM and InnoDB.
But note: out of 185,082 rows, 17 have "O'Brien". That's less than 50%, so the search term is not ignored. I verified the count with LIKE "%O'Brien%".
But, when adding the +, it needs to be outside the quotes for InnoDB. (Yet another difference between the implementations.)
(I have updated my blog to add this ...
1
If we create the table with the Aria or MyISAM storage engine, then the query succeeds:
CREATE TABLE customer2 (
name TINYTEXT NOT NULL,
FULLTEXT (name)
) ENGINE = Aria;
INSERT INTO customer2 VALUES ('O''Brien');
SELECT * FROM customer2 WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
1 row in set (0.001 sec)
CREATE TABLE customer3 (
...
1
You can use Conditional ORDER BY and then use LIMIT 1 to restrict result-set to one row. This approach depends on a MySQL feature where it implicitly typecasts the result of a conditional expression, i.e., boolean to int (0/1)
SELECT * FROM emails
WHERE primary_key=?
ORDER BY
user_id = ? DESC, -- if user_id matches, it will be 1 and come first
...
1
One way is to use a Derived Table (subquery) and get all the maximum id values for every unique remote_id value. We can then JOIN this subquery to the original table to get the rows corresponding to highest id.
SELECT
t.*
FROM table_name AS t
JOIN (SELECT
remote_id,
MAX(id) AS max_id
FROM table_name
GROUP BY remote_id) AS ...
1
Very often joining to a VALUES clause improves performance in these cases:
SELECT *
FROM "TranslationText"
JOIN "Phrase" ON "TranslationText"."phrase" = "Phrase"."id"
[.. your other joins ...]
JOIN (
values
($1, $2, $3, $4),
($5, $6, $7, $8),
....
) as v(org_text, org_lang, new_lang, description)
on v.org_text = "...
Only top voted, non community-wiki answers of a minimum length are eligible
