Questions tagged [query]

Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).

Filter by
Sorted by
Tagged with
4 votes
4 answers
320 views

How to update table records in reverse order?

I've a table Student Id Name Mark 1 Medi 10 2 Ibra 15 3 Simo 20 and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order: ...
0 votes
1 answer
14 views

How to order by cumulative value?

In my application widgets move step by step through a workflow. I want to list the widgets ordered by their step due-dates but I can't figure out how to do that. There are many workflows. Each ...
0 votes
1 answer
588 views

Nested push in Mongoose

I have a user schema like that: name: String, nick: String, email: String, password: String, bio: String, color: String, followers: [String], followings: [String], posts: [{ author: { ...
0 votes
1 answer
27 views

Find columns where change happens

I am pretty basic in my sql code, but I am trying to find a way to decide when a change occurs in a set of data. I have a 3 columns where column 1 (AgreementNr) can have many records. Each of these ...
0 votes
0 answers
17 views

Writing SQL with selected value as title and calculated sub-total

I need help writing my SQL query. I've got 3 tables. Here's a simplified example. Items Name Description First desc 1 Second desc 2 Item Detail Name Supplier Reorder Level First Amazon 1 Second ...
-1 votes
0 answers
33 views

SQL insertion optimization

I have an sp with the below code Insert into #temp(item_id,equip_id) Select m.item_id, equipemjtid from dbo.spares m inner join #temp1 b on b.id=m.objectid But just this query is taking too much ...
0 votes
1 answer
17 views

How to filter a table remove duplicated keys and keep unique value for each key in one table?

The input data and output example as the picture. I tried to use pandas to filter the data after I try query: select * from tablename; is there any better way like only using a SQL query to do this ...
1 vote
0 answers
36 views

Query to Extract all Values below Certain Amount ignores 0-values

Trying to write a query in SqlServer Dev 2017 with SSMS with Object Explorer. I'm trying to extract all members ( PK) with totals ( Sum(ColumnValues)), below a certain amount. My code extracts all ...
5 votes
3 answers
74 views

Select telemetry data based on relational data in PostgreSQL/TimescaleDB

I'm trying to solve a particularly difficult problem. I am storing some telemetry data from some sensors in an SQL table (PostgreSQL) and I want to know how I can I write a query that will group the ...
0 votes
0 answers
9 views

Question regarding Update Query to increment values in Table

I have a table with a coloumn which contains data of the following structure '2601487N0001000','2601487N0001001', '2601487N0001002'...... etc however, due to some reason, the table has some duplicate ...
-4 votes
0 answers
27 views

SQL help requested

I have a query I need to modify to bring in another translation from a code to a text field in a translation table (Code_Desc) It currently works with a single translation, I get errors when trying to ...
0 votes
0 answers
11 views

Table Prefix Problem or Not?

I am trying to determine if I actually have a db table prefix problem or not in my wordpress db. I am on mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu)). Status Quo: I changed my ...
-2 votes
1 answer
47 views

CASE clause doesn't work on column sql server [duplicate]

this is my query SELECT Name, Salary, ReportsTo, CASE WHEN ReportsTo = NULL THEN 'Manager' ELSE 'Employee' END AS R FROM Employees but when I run it, the R column shows ...
0 votes
1 answer
35 views

postgresql - delete duplicate object in json

I need to remove duplicated objects in a jsonb field in table in PostgreSQL 12.7. How can I do this for all rows in table? In this example I need to remove the 3th and 4th object (date: 2022-09-12) [ ...
0 votes
0 answers
17 views

Check Query & Populate Another Table

I want to write a query that checks a table that has an ETL execution information specifically the Execution End Time (there's a column for that) and go ahead and populate the Execution End Time into ...
0 votes
0 answers
14 views

MySQL: Querying WHERE IN a UNIONed selection of ids is slow, but creating and querying against a temp table with that same UNIONed selection is quick

Running this query is instant (and results in 2 rows) SELECT model_id as id FROM jobs WHERE jobs.batch_id IN (999) AND jobs.model_type = 'App\\Project' UNION ...
0 votes
3 answers
51 views

Merge 2 rows into one row

| Year | Category | Number_of_products | | 19 |Fruits1 | 25 | | 19 |Fruits2 | 45 | | 20 |Fruits1 | 50 | | 20 |Fruits2 | 50 ...
-1 votes
0 answers
9 views

Interupt during rebuild

What happens if rebuild query gets interuptted.Does it begin with transaction?. I want to perform Rebuild without using any options.Options like resumable online index rebuilds
1 vote
1 answer
39 views

How two get summary from different table with same field per table

I have two table like this As You can see the table is same field. And the different just value on additional_date on table tutorial [{"cash": 100}, {"cash_other": 2000}] and [{&...
0 votes
1 answer
21 views

How do I query a large time-series table in MySQL?

I have a Zabbix database with 2 tables, a items table that stores items, and a history_text table that stores time-series values about the items. I am trying to create a query that will fetch the ...
1 vote
2 answers
2k views

MySQL do not accepts wildcard for tables name level grant

Trying to grant a user select privilege on multiple tables using wildcards as following: GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'; But keep giving the following ...
0 votes
1 answer
251 views

postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like ...
0 votes
0 answers
13 views

Updating a multivalue field in UniData (U2) in bulk?

In a UniData database (U2), let's say I have a file named FILE1, which contains a field named FIELD1, and the field is a multivalue field. FIELD1 might contain values like this: CO AB X P AB ABC The ...
0 votes
2 answers
41 views

in PostgreSQL REPMGR Master-Slave environment is it necessary to have same H/W config for HOT STANDBY setup?

I have a PostgreSQL repmgr Master-Slave setup with HOT standby enabled. In such repmgr configuration isnt it possible to configure the postgresql.conf in Primary and secondary DIFFERENTLY WHILE HOT ...
0 votes
0 answers
23 views

IN restrictions are not supported on indexed columns

I pull cassnadra:latest image in docker and make container. when I exec container and run following query: select count(*) from items where item_id in (ef2ca439-ce2a-4254-96d2-dc0c3bbed85c, c190676f-...
2 votes
1 answer
43 views

Optimizing performance of subqueries

I am fairly new to MySQL and I am trying to return a result of a query based on a subquery, but I notice very poor performence. This is the statement I wrote: SELECT value FROM `history_text` WHERE ...
0 votes
1 answer
42 views

Querying large tables in postgresql

Got a problem querying two large tables on postgres, both of them having a indexed column to identify the year, wich i'm using to reduce the number of rows, something like this: WITH table_1 AS (...
-1 votes
1 answer
30 views

MySQL query: Detect different rows between two tables and show differences in result table

I have two tables in a MySQL database. Table1 a_id 1 3 4 5 6 Table2 b_id 1 2 3 I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
-1 votes
1 answer
30 views

Subtract current time value with last 1 hour time value

Table Name: Data Time Data Result 2022-09-05 07:45:10 12.0 9.0 2022-09-05 07:44:15 10.0 8.0 2022-09-05 07:43:12 13.00 12.0 2022-09-05 07:42:11 31.0 29.0 2022-09-05 07:41:15 13.0 12.0 2022-09-05 ...
0 votes
1 answer
164 views

How to compare two last updated entities from two different table Postgresql?

I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new ...
0 votes
1 answer
226 views

Set the file name in "SELECT ... INTO DUMPFILE"

I am trying to write a query to dump a single LONGBLOB data into a file. SELECT data FROM table WHERE id = 123 INTO DUMPFILE fileName which data, fileName, and id are the fields in the table. SQL ...
3 votes
2 answers
19k views

Alternate to use LIMIT in subquery in mysql?

I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to ...
-4 votes
1 answer
24 views

How select join sum with 0 returned if not exist

I need some advice. I want to select join with sum. select year(date), month(mon), c.id, c.name, sum(t.trx_detail) from client c left join trx t on c.id = t.id group by year(date), month(mon), c.id, ...
1 vote
1 answer
2k views

Postgres HAVING ilike any wildcard in array_agg

I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is: SELECT p.id, p.name, array_agg(vo.name) FROM product p LEFT ...
-1 votes
1 answer
39 views

LEFT JOIN not return right value

I am trying to join 3 tables with it foreign keys, but for some reason one of the return value from the query doesn't return correct when I use LEFT JOIN for the 3 tables but I removed the one that ...
0 votes
1 answer
179 views

Search in JSON by id and name

I have this table: id number name text row for example: id name 1 {"vals":[{"id":123,"name":""}],"additional":"text"} 2 {"vals":[{...
1 vote
1 answer
54 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
1 vote
2 answers
65 views

MySQL: Improve performance on large table for dynamically built query with composite index correctly

Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
0 votes
1 answer
334 views

how can I extract values from one cell to make it multiple rows

if i had a table like this: ------------------------------------------ user | list_of_jobs | ------------------------------------------ 1 | [dentist] | [farmer] | [fisher]...
0 votes
1 answer
650 views

SQL Get All Data With The Given IDs And With The Same Data On Single Column

So, basically, I have this table +------------------- |id | col1 | name | |----|------|------| | 1 | a | la | | 2 | b | ria | | 3 | w | la | | 4 | q | la | | 5 | y | ria | ...
1 vote
1 answer
2k views

MongoDB slow query, $regex + sort, 30 million documents

I have a collection that has 30 million documents, and I am trying to do a live search that selects the documents by a keyword and sorts them in a certin matter (it shows first users that have a ...
0 votes
1 answer
276 views

Query to select specific columns based on current date

I have a table named 'mrtg' that has four columns: mysql> select * from mrtg; +----------+------+------+------+ | city | day1 | day2 | day3 | +----------+------+------+------+ | Delhi | ...
1 vote
2 answers
181 views

How can MySQL return an empty row matching a given table's schema, given the table name as an input string?

How can a query or procedure return an empty copy of a new record? Ideally suited for objects which are in a transactional allocation process: It is highly preferable to have authoritative defaults ...
-1 votes
1 answer
26 views

How to Verify Backup Archive in MySQL

I would like to know if there is any way to verify MySQL backup files. (Check if the file is valid or not for restore). Thanks!!
1 vote
1 answer
97 views

Query optimisation to remove nested loop

I have below query which is having high Sub-tree cost. I have created appropriate Indexes for for the query which are getting seek but, still the sub-tree cost in high. however, I can see nested Loop (...
0 votes
1 answer
228 views

Creating a parent table from child relationships

I have a child relationship table as CREATE TABLE ItemRelationships ( Item1 int(11) unsigned NOT NULL, Item2 int(11) unsigned NOT NULL, INDEX(Item2), FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON ...
0 votes
1 answer
181 views

how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

The query I am using to retrieve data is as "select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb," + " sum(totalqt) as tqdb," + " sum(payed) as tpdb," + " sum(...
0 votes
1 answer
199 views

WordPress and WooCommerce very slow query

After a migration to a new WooCommerce install, but we are experiencing very sow queries. Sometimes more than 1 minte. Currently this query is fired. This is fired in 0.7253s EXPLAIN SELECT t.*, tt.*, ...
2 votes
1 answer
272 views

Why SERIALIZABLE level does not lock the table for other session in SQL Server

I want to Prevent concurrency in my table for Delete, Insert, Update, and Select so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
1 vote
1 answer
579 views

Complex queries in CouchDB across multiple documents

I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something. Assume the following (simplified) document examples: { "docType"...

1
2 3 4 5
28