Hot answers tagged

3

It seems you need in simple SELECT *, SUM(debit_balance - credit_balance) OVER (PARTITION BY account_no ORDER BY id) balance FROM acc_account_transactions fiddle


3

SELECT t1.TagID AS TagID1 ,t2.TagID AS TagID2 ,COUNT(1) FROM TagMap AS t1 JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID GROUP BY t1.TagID, t2.TagID UPDATED: index on ArticleID may help to improve query performance


3

Another solution is one table per quantity. +-----------+------------+-----------+ | device ID | date | quantity1 | +-----------+------------+-----------+ | A | 2020/06/03 | 10 | +-----------+------------+-----------+ +-----------+------------+-----------+ | device ID | date | quantity2 | +-----------+------------+-----------+ | ...


2

Short answer: It's 2020, the case for spinning rust is super-weak. Long answer: It depends on how much memory you have and what your read:write ratio is. If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will ...


2

Note: Try to install MySQL same version, if your crashed MySQL version was 5.6, please install MySQL 5.6. (brew install mysql@5.6). If following process fails try the same things with MariaDB. (In my case, by MariaDB, I get back all database) Process 1 Before start, must stop MySQL or MariaDB. Use this command (brew services stop mysql), For MariaDB brew ...


2

If I understand correctly, you have hundreds of measuring devices, and you're contemplating having a table for each one, or one table for all -- is that correct? And for the one-table solution, you're thinking of a design something like this: +-----------+------------+-----------+-----------+-----------+-----------+-----------+ | device ID | date | ...


1

Database schema and data CREATE TABLE `Students` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(32) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Students` (`ID`, `Name`) VALUES (1, 'Ashley'), (2, 'Samantha'), (3, 'Julia'), (4, 'Scarlet'); CREATE TABLE `Friends` ( `ID` int(10) unsigned NOT NULL ...


1

WITH cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1, UDF1(1, a, b, c, d) AS Foo2, UDF1(2, a, b, c, d) AS Foo3, UDF1(3, a, b, c, d) AS Foo4, x, y, z FROM MyTable ), cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z, UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5, ...


1

1 IN (a,b) won't scale. It will do a full table scan. This will make use of indexes: ( SELECT id, receiver_id FROM messages WHERE sender_id = 1 LIMIT 3 ) UNION ALL ( SELECT id, sender_id FROM messages WHERE receiver_id = 1 LIMIT 3 ) LIMIT 3 together with both INDEX(sender_id, receiver_id, id), INDEX(receiver_id, sender_id, id) Now, assuming you ...


1

Your query was only missing the ability to reset @running_balance. You can only reset @running_balance when the row has a different account_no from the previous row. With this in mind, I give you (drum roll please) ... PROPOSED QUERY SELECT account_no,debits,credits,balance FROM (SELECT account_no ,COALESCE(debit_balance) as debits ,COALESCE(...


1

I notice that when I accidentally put ' in it ... the console seems to stuck there It's not "stuck" at all. It's patiently waiting for you to supply the closing quote for the string (or date) literal that you started when you typed in the opening quote! It even goes as far as to give you a "hint" that it's doing so - notice the different prompt it ...


1

How about this : select t1.key from table1 t1 inner join table1 t2 on t1.key = t2.key || 'P' where t1.key like '%P' order by t1.key ; Querying t1 gives you a list of all of the rows with keys that end in 'P'. The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1 (or, alternatively, the original key ...


1

What you want is called an UPDATE JOIN First you must create a query that has the MAX(b_foo) for every a_uid: SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid; Making this a subquery, you can perform the UPDATE JOIN as follows: UPDATE A INNER JOIN (SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C ON A.id = C.a_uid SET A.foo = C.max_b_foo;...


1

The syntax for CAST() is CAST(expr AS type [ARRAY]). You got the syntax right but you were unable to do the cast because TINYINT is not among the list of permitted values for 'type' in MySQL. Casting to INT works in MariaDB but it doesn't in MySQL. MySQL only allow any one of the following to be used as value for 'type': - BINARY. - CHAR - DATE - DATETIME - ...


1

For a closer analysis we would need the table definitions for both tables and indexes as well as the row counts, but I think the problem is that you use key2 for joining and mysql can then not use it for filtering of the datetime (since the first part of the index, the key2, is different for the rows in the join result). Can you try to create an index on ...


1

SELECT t1.run_name, hardware_used, COUNT(*) times_used FROM hardware t1 JOIN hardware t2 USING (hardware_used) WHERE t1.run_name >= t2.run_name GROUP BY t1.run_name, hardware_used ORDER BY t1.run_name; fiddle


1

Testing out $tompave's Comment; it seems to work: First, create and populate: mysql> CREATE TABLE test_uuid ( -> my_uuid BINARY(16) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID())); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test_uuid (...


1

In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row. Here's an example: DECLARE @c int; DECLARE @c_base64 varchar(8); DECLARE @c_converted int; --get the cryptographically random integer SELECT @c = CONVERT(int, crypt_gen_random(4)); --convert it into a base-64 string DECLARE ...


1

You use a subselect query as basis for t1 and t2 Like SELECT t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1) FROM (SELECT * FROM TagMap WHERE TagID IN (SELECT TagID FROM Tags WHERE Articles > 50)) AS t1 JOIN (SELECT * ...


1

You could use the window function row_number, like so: select id, collection, name from ( select id, collection, name, row_number() over (partition by collection order by id) rn from stac_item ) t where rn < 3 order by id


1

Unclear. What does drink_no signify? You should not try to maintain a "running count" of how many drinks a person has consumed on a given day. Instead, simply store that they have consumed another drink and let your queries do the "adding up": select drinker , odate , count( odate ) total from table1 group by drinker, odate order by drinker, odate ; ...


Only top voted, non community-wiki answers of a minimum length are eligible