I have below dictionary values. Example: dic = {'ID1': ['Name1', 'Name2'], 'ID2': ['Name1', 'Name3'], 'ID3': ['Name2']} I want to print address of every matched names from above dict values into below format. Example Output: ['ID1', 'adress1' ,'adress2'], ['ID2', 'adress1' ,'adress3'], ['ID3', 'adress2'] Below is my example code of database table that has "Emp_ID", "Emp_Name", "Emp_Address". The dict values are "Emp_Names", that should be match emp_Names and get the Adresses in to the list as Example out. import sqlite3 conn=sqlite3.connect('emp.db') conn.execute("""CREATE TABLE IF NOT EXISTS branch (Emp_ID TEXT UNIQUE, Emp_Name TEXT UNIQUE, Emp_Address TEXT )""") conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \ VALUES('Emp1', 'Name1', 'adress1')""") conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \ VALUES('Emp2', 'Name2', 'adress2')""") conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \ VALUES('Emp3', 'Name3', 'adress3')""") conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \ VALUES('Emp4', 'Name4', 'adress4')""") conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \ VALUES('Emp5', 'Name5', 'adress5')""") conn.commit() conn.close() For example 'ID1' (column) should not to be changed. And 'name1' should take its address and 'name2'should take its address and all matched names should take their addresses. Which statements are useful for my scenario. Is it possible? If possible how to to?

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative name. All names are ranked and the one with the highest ranking-position shall be used. Unranked names are “0” or “-1” and should be ignored and since the name-ranking-system is bad, the one to use is not always “1”. In the case of no name being there, the main entry should still be returned. In short: I need a left join that takes all entries of table “main” and joins them with the name that has the smallest, greater than 0, position, if there is one. main:

| main_ID | val_A | val_B |
+---------+-------+-------+
|       2 | some  | stuff |
|       3 | and   | more  |
|       4 | even  | more  |

names:

| name_ID | main_ID | name           | position |
+---------+---------+----------------+----------+
|       1 |       2 | best name      |        1 |
|       2 |       2 | some name      |        0 |
|       3 |       3 | alt name       |        3 |
|       4 |       2 | cool name      |        2 |
|       5 |       3 | abandoned name |       -1 |
|       6 |       3 | awesome name   |        2 |

what I want to get:

| main_ID | val_A | val_B | name         |
+---------+-------+-------+--------------+
|       2 | some  | stuff | best name    |
|       3 | and   | more  | awesome name |
|       4 | even  | more  |              |

I have a table with two main columns, id1 and id2: drop table if exists pairs; create table pairs (id1 int, id2 int); insert into pairs values (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8); I want to select rows which have these pairs [(1,2),(5,4),(3,1)]. This array could have more than 500 items, so how I can write a select query to select those rows? I am using 'Netezza' database but I need to find the logic to do this, I can figure out how to implement this logic in my database engine.

We have two tables in our MYSQL 5.7 Aurora database: CUSTOMER_ORDER and BATCH. Customer order can have only one batch associated and it is not mandatory to have one. Create table statement of CUSTOMER_ORDER table: CREATE TABLE 'CUSTOMER_ORDER' ( 'CLIENT_ID' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'ORDER_ID' varchar(64) COLLATE utf8mb4_bin NOT NULL, 'ORDER' json NOT NULL, 'ORDER_DATE' date GENERATED ALWAYS AS ( cast(json_unquote(json_extract('ORDER', '$.date')) as date) ) VIRTUAL, 'TEAM_ID' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('ORDER', '$.teamId.teamId')) ) VIRTUAL, 'ORDER_SOURCE' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('ORDER', '$.orderSource')) ) VIRTUAL, 'ORDER_STATUS' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('ORDER', '$.status.status')) ) VIRTUAL, 'EFFECTIVE_STATUS' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('ORDER', '$.effectiveStatus')) ) VIRTUAL, 'CREATED_ON' timestamp(6) NOT NULL, 'UPDATED_ON' timestamp(6) NOT NULL, 'ADDED_ON' timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), 'BATCH_ID' varchar(128) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('ORDER', '$.batchId.batchId')) ) VIRTUAL, PRIMARY KEY ('CLIENT_ID', 'ORDER_ID'), KEY 'order_date_team_idx' ('CLIENT_ID', 'ORDER_DATE', 'TEAM_ID') ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin Create table statement for BATCH table: CREATE TABLE 'BATCH' ( 'CLIENT_ID' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH_ID' varchar(128) COLLATE utf8mb4_bin NOT NULL, 'BATCH_DATE' date NOT NULL, 'BATCH_STATUS' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH_SLA' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH' json NOT NULL, 'EMPLOYEE_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'EMPLOYEE_PERSONA_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'VEHICLE_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'VEHICLE_MODEL_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'RECORD_VERSION' int(11) NOT NULL, 'CREATED_ON' timestamp(3) NOT NULL, 'UPDATED_ON' timestamp(3) NOT NULL, 'ADDED_ON' timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), 'MINIMAL_BATCH' json DEFAULT NULL, 'BATCH_ID' varchar(64) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('MINIMAL_BATCH', '$.batch.planId.sourceId')) ) VIRTUAL, 'PLAN_ID' varchar(64) COLLATE utf8mb4_bin GENERATED ALWAYS AS ( json_unquote(json_extract('MINIMAL_BATCH', '$.batch.planId.planId')) ) VIRTUAL, PRIMARY KEY ('CLIENT_ID', 'BATCH_ID'), KEY 'date_rider_idx' ('CLIENT_ID', 'BATCH_DATE', 'EMPLOYEE_ID') ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin And I am using the following query to find out the count of customer orders for a given client for a given date: SELECT COUNT(1) FROM CUSTOMER_ORDER AS customer_order INNER JOIN BATCH AS batch ON customer_order.CLIENT_ID = batch.CLIENT_ID AND customer_order.BATCH_ID = batch.BATCH_ID WHERE customer_order.CLIENT_ID = 'clientA' AND ORDER_DATE = '2021-05-01'; The reason I am doing this left outer join is to do further filtering of customer orders based on the batch. The problem I am facing with this query is that it takes in order of minutes to execute this query for clients who have large number of customer orders(~20k-100k) for a given date even without any extra filters on the batch table. The output of the EXPLAIN statement for the query is as given below: id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra 1,SIMPLE,customer_order,NULL,ref,"PRIMARY,order_date_team_idx,batch_idx",PRIMARY,130,const,1,10.00,"Using where" 1,SIMPLE,batch,NULL,eq_ref,"PRIMARY,date_rider_idx,team_id_idx",PRIMARY,644,"const,locus_devo.customer_order.BATCH_ID",1,100.00,"Using index" Can you please help me identify the root cause of underperformance of this query?

I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month On a daily it should run a select * from subscription_x to get data from the table of the previous day e.g if today is Dec 14th it should run select * from subscription_13 then tomorrow Dec 15th it will run select * from subscription_14 Can someone help please need a way to do this in sql

On what basis should I compare performance of queries? Query 1: select * from dbo.Table1 where [T1_col1] like '%string%' Execution Plan Query 2: select Table1.* from Table1 left join db2.dbo.Temp on Table1.num=db2.dbo.Temp.num where db2.dbo.Temp.[col1]='string' Execution Plan Query 3: select * from dbo.Table1 where num in (select num from db2.dbo.Temp where [col1]='string') Execution Plan Query 1 is my actual query that executes thousands of time in a day. It being **non sargable** , I want to convert it into a sargable query (creating child table and querying it), hence query 2 and 3. The problem now is I am not sure how to compare the performance of these queries. The tracer, client statistics and statistics IO values I have given **are not constant** (for example queries will have varying CPU time of 0 and 12ms each time I execute the query).All three methods gave different results. Time and IO statistics = ON I get following results Query 1: Scan count 1, logical reads 1987, physical reads 2, read-ahead reads 390, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 12.4 ms, elapsed time = 213 ms. Query 2: Scan count 0, logical reads 1882, physical reads 1, read-ahead reads 265, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3.2 ms, elapsed time = 175.2 ms. Query 3: Scan count 0, logical reads 1892, physical reads 1, read-ahead reads 265, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3 ms, elapsed time = 166.6 ms. Tracer result: Client Statistics: So according to these 3 methods I'm not able to derive a conclusion as to which of my queries would perform better. All the queries were run on a database have 50000 rows of data. Any solution for this? Feel free to ask for any other details if required.

Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select? (assuming unique alphanumeric index and no auto-increments if these matter)

I have one Select SUM command that works: # SIM Orders Pending by Customers for 3101708 IMSI SIMs with 5YY MSISDNs SELECT accountname AS 'Account Name', IF ((SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NULL , 'N','Y') AS '5YY MSISDN', LPAD(CONCAT(FORMAT(SUM(quantity), 0)),15,' ') AS 'Quantity' FROM T3101708 WHERE outputfilereceived IS NULL AND (SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NOT NULL GROUP BY accountname, accountid ORDER BY SUM(quantity) DESC; After running this, I get following result: +---------------------------------+------------+-----------------+ | Account Name | 5YY MSISDN | Quantity | +---------------------------------+------------+-----------------+ | FCA - SXM - AT&T | Y | 48,000 | | Numerex - AT&T | Y | 34,000 | | Mytrex Inc. - AT&T | Y | 24,000 | | Honda US - AT&T | Y | 18,000 | +---------------------------------+------------+-----------------+ 3 rows in set (0.03 sec) Based on the result above, I want to SUM the last column by having following Select SUM command: SELECT SUM( SELECT SUM(quantity) FROM T3101708 WHERE outputfilereceived IS NULL AND (SELECT * FROM 5YYAccounts WHERE 5YYAccounts.accountid = T3101708.accountid) IS NOT NULL GROUP BY accountname, accountid ); For this time, I am getting an error in mySQL: > ERROR 1064 (42000): You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right > syntax to use near 'SELECT SUM(quantity) > FROM T3101708 > WHERE outputfilereceived IS NULL AND' at line 2 What did I code wrong here? Thank you. Bobby

I have this two working queries: select distinct(foo) from my_table; +--------------------------------------------------+ | foo | +--------------------------------------------------+ | bar1 | | bar2 | | ... | +--------------------------------------------------+ And: select foo, sum(position)/count(position) as average_position from (select foo, position from my_table where foo = 'bar1' order by date desc limit 3) as subquery group by foo; +------------------+------------------+ | foo | average_position | +------------------+------------------+ | bar1 | 8.3333 | +------------------+------------------+ And now I would like to do this in one request, so I want to do my second request for each distinct foo in one request. OUTPUT must be: +------------------+------------------+ | foo | average_position | +------------------+------------------+ | bar1 | 8.3333 | | bar2 | 7.0000 | | ... | ...... | +------------------+------------------+ How I can achieve this?

Just checking I'm understanding this correctly: CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, name TINYTEXT NOT NULL, PRIMARY KEY (id), FULLTEXT (name) ) ENGINE = InnoDB; INSERT INTO customer VALUES (1, "ABC.DEF"); INSERT INTO customer VALUES (2, "ABC_DEF"); INSERT INTO customer VALUES (3, "ABC'DEF"); INSERT INTO customer VALUES (4, "ABC.DE"); INSERT INTO customer VALUES (5, "ABC.DFF"); Where I've got innodb_ft_min_token_size set to 3 (default is 4). --- When running: SELECT c.*, MATCH (name) AGAINST ("+ABC +DEF" IN BOOLEAN MODE) AS m FROM customer AS c Customers 1 and 3 match, because the . and ' are seen as word separators ([annoying for O'Brien](https://dba.stackexchange.com/q/250524)) . For customer 2, because the underscore gets the whole name treated as a single word, the "DEF" word cannot be found. --- If I change the MATCH to "+ABC +DE". 1, 2, or 3 do not match because this is using a full word match ("+DE" does not match "DEF"). 4 does not match because... innodb_ft_min_token_size is set to 3? As in, the 2 letter "DE" word is not in the FULLTEXT INDEX? --- If I change the MATCH to use asterisks (e.g. "+ABC* +DE*"), that will use *prefix* matching. But will only add customers 1 and 3 to the selection. Because the 2 letter "DE" word for customer 4 is not in the FULLTEXT INDEX? --- If I change the MATCH to use "+ABC.DE*", it matches all of them. Note how they all get the same rank (even customer 5), and this is no different to "+ABC*", where MATCH seems to be seeing the "DE*" as a separate word, and not matching it against anything. Whereas "+ABC DE*" is explicitly keeping it as a separate word, and the scores are handled appropriately. --- While the individual points make sense, I'm not sure this creates a good system. For a bit more consistency, I'm wondering if the database should ignore short words (tokens) in the MATCH query, in the same way it does when building the FULLTEXT INDEX. Only because I don't think "+DE" will ever do anything useful when the min token size is 3; and it's not exactly easy for the developer to identify what the individual words in the FULLTEXT INDEX will be (i.e. to remove them).

I've been trying to learn CTEs and having a bit or trouble getting the hang of them. I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. Here is the overblown (and redundant) SQL that I came up with: SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer, A.phone, F.title, date(R.rental_date) AS rental_date FROM sakila.rental R LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id LEFT JOIN sakila.film F ON I.film_id = F.film_id LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id LEFT JOIN sakila.category C ON FC.category_id = C.category_id LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id LEFT JOIN sakila.address A ON CU.address_id = A.address_id WHERE CU.customer_id in (SELECT CU.customer_id FROM rental R LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id LEFT JOIN sakila.film F ON I.film_id = F.film_id LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id LEFT JOIN sakila.category C ON FC.category_id = C.category_id WHERE C.name = "Horror" GROUP BY CU.customer_id HAVING COUNT(CU.customer_id) >= 3) AND C.name = "Horror" ORDER BY customer, title, rental_date DESC; And here are some of the results in my database client (Navicat for MySQL ): **Is there a way to rewrite the query using a CTE?** It seems like just the type of query that a CTE would be perfect for, if only I could figure it out! Thanks!

I'm searching for a way to keep track of any SELECT statements on a specific table in a database where logs are not enabled. I can't find any solutions to this except to force people to use a stored procedure to select data from the table and then log entries within that SP. Is there a better solution to this problem? I do have full rights over processlist if that helps but given that the table I want to keep track of is pretty small, there's a good chance I may miss the queries between calls.

cities | id | name | county_id | |----|:------:|----------:| | 1 | City1 | 1 | | 2 | City2 | 1 | | 3 | City3 | 2 | | 4 | City4 | 2 | counties | id | name | |----|:-------:| | 1 | County1 | | 2 | County2 | Hello,
I would like to select all city names from related county by enetring a city name.
I can do it via subquery, but is there any other way to achieve this?

SELECT name
FROM cities
WHERE cities.county_id =
      (SELECT county_id
       FROM cities
       WHERE name = 'City1'
       LIMIT 1);

result => "City1, City2"

Thank you.

I have the following model: Table user: (id, name, direct_manager_id) Table customer: (id, name, managed_by_id) i.e. users can have another user as their direct manager, Customers can be managed by a user. My requirement is to create a simple select query that will retrieve all the customers that are managed by a specific user. moreover I would like that query to retrieve all the customers that are managed by users that their direct manager is that same user (1 level deep of direct management). and to retrieve all the customers that their direct manager is manged by a user that the direct manager of him is the targeted user (2nd level deep of direct management).... (to the nth level deep) Here is a picture of the hierarchy: targeted User / \ user1 user2 / \ / \ c1 c2 c3 user3 / c4 So the select query with the targeted user id will select the customers c1-c4

When executing the following statments on MariaDB 10.6.14 SET @json_1 = JSON_OBJECT('id', 'name'); SET @json_result_1 = JSON_OBJECT('test', @json_1); SELECT @json_result_1; SET @json_result_2 = JSON_OBJECT('test', JSON_OBJECT('id', 'name')); SELECT @json_result_2; I receive the following results: For @json_result_1: {"test": "{\"id\": \"name\"}"} And for @json_result_2: {"test": {"id": "name"}} How does it come that I retrieve different result? Aren't those JSON string supposed to be internally all treated as TEXT?

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, therefore, HH: 55 will always be the maximum hourly total that I need to list for the day DD-MM-YYYY Is it possible to list in the second SELECT a daily or monthly total of the data stored in this way? If so, how? Thank you very much. EDIT: Mysql version: 5.7.32-35-log I save the data every 5 minutes, but at the end of HH: 55 there is always the highest value for the hourly total precipitation. So I have: id | rain | timestamp 1 | 0.0 | 2021-10-04 12:00:00 2 | 0.0 | 2021-10-04 12:05:00 3 | 0.6 | 2021-10-04 12:10:00 4 | 0.9 | 2021-10-04 12:15:00 5 | 0.9 | 2021-10-04 12:20:00 6 | 1.4 | 2021-10-04 12:25:00 ........ 12 | 2.5 | 2021-10-04 12:55:00 // MAX rain 13 | 0.0 | 2021-10-04 13:00:00 // new hour and clean rain to 0.0 14 | 0.0 | 2021-10-04 13:05:00 15 | 0.6 | 2021-10-04 13:10:00

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns id int(11) NOT NULL, time int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY id_time (id,time) so there can be multiple timestamps per id and multiple ids per timestamp. I'm trying to set up a query where I get all entries plus the next greater time for each entry, if it exists, so it should return e.g.: +-----+------------+------------+ | id | time | nexttime | +-----+------------+------------+ | 155 | 1300000000 | 1311111111 | | 155 | 1311111111 | 1322222222 | | 155 | 1322222222 | NULL | | 156 | 1312345678 | 1318765432 | | 156 | 1318765432 | NULL | +-----+------------+------------+ Right now I am so far: SELECT l.id, l.time, r.time FROM idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id WHERE l.time l.time and not only the first one... I guess I'll need a subselect like SELECT outer.id, outer.time, (SELECT time FROM idtimes WHERE id = outer.id AND time > outer.time ORDER BY time ASC LIMIT 1) FROM idtimes AS outer ORDER BY outer.id ASC, outer.time ASC; but I don't know how to refer to the current time (I know the above is not valid SQL). How do I do this with a single query (and I'd prefer not to use @variables that depend on stepping though the table one row at a time and remembering the last value)?

I know the web is full of questions, complaints, etc. like my question. But each of them still does not answer the questions of people finding those posts via Google, ... I try to post my question with an example but would like to find some generic advices for possible reasons for this kind of problems. I have a table of this structure (there are some foreign keys) id uuid [uuid_generate_v4()] locationId uuid NULL orderItemId uuid NULL createdAt timestamptz amount numeric(10,4) NULL quantity integer NULL productId uuid NULL bookingId uuid NULL courseId uuid NULL courseUnitId uuid NULL courseModuleId uuid NULL courseCategoryId uuid NULL courseCategoryIds jsonb NULL [[]] kind character varying discountRuleId uuid NULL inquiryId uuid NULL The table is filled with about 3000 rows (which seems not much to me). But when I run this query

* from stat_record

the result is provided after about 20 seconds. If i decrease the number of selected fields (like

id, "locationId from stat_record") the processing time increases linear-relative - I double the amount of selected fields, the waiting time will be doubled also approximately)

What could be potential reasons for this?

 1. Can indizes help in this case? Or are indizes only helping with performance while joining over tables?
 2. Damaged/inconsitent tables? What is a good starting point to learn about VACUUM, INDEX, REINDEX?
 3. ... ?

Execution plan:

explain (analyze, buffers, format text) select * from stat_record``` gives Seq Scan on stat_record (cost=0.00..122.12 rows=3812 width=225) (actual time=0.011..0.636 rows=3812 loops=1) Buffers: shared hit=84 Planning Time: 0.102 ms Execution Time: 0.998 ms

Im using this script for updating "gndt_customer_identification" table, value of V_iden_no coming from select statement. How i can update multiple row when select return more then one value for V_iden_no ???

Update gndt_customer_identification
set V_iden_code = 'CNIC', 
    V_iden_no = (
        SELECT SUBSTR (V_iden_no,1,13)
        FROM gndt_customer_identification
        WHERE V_iden_code = 'DM_OT'
        AND V_iden_no LIKE '_____________ _%' 
        and N_CUST_REF_NO =10434997
        )
where N_CUST_REF_NO =10434997;

I'd like to know if it is possible in mysql to grant select permission to user but only within specific hours of a day. I have created several views and new user and want to let them browse those mysql views only (for example) between 10pm and 11pm.