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
-1
votes
1answer
50 views

Long Running Query Optimization

I need to optimize this query as this query is taking minutes of time in execution. Below is the query. i'm giving date parameters and shift id is optional. as query is returning same in and out from ...
0
votes
0answers
16 views

How can I write a query avoiding intermediate views/steps from different tables in PostgreSQL?

I am working in a large query I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one. The first query (QUERY 1) select the grid id from a table called ...
0
votes
0answers
6 views

Delete JSON object from array, in multiple columns, post

CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"AAA", "...
1
vote
1answer
49 views

What would be the best way to conditionally insert or update a table that has this unique design?

I have an items table that has columns for person ID, position #, and individual lists of items that really aren't associated with the other fields in the record. I have no control over the structure ...
1
vote
2answers
33 views

Select where combined with exists

Table dating_users: user_id | username ---------+---------- 11 | taimoor 13 | jacob 14 | pi (3 rows) Table dating_likes: like_id | user_id | liked_user ---------+---------+--------...
0
votes
3answers
23 views

Query to fine “Order_num” that has no two equal “product_name”

I have a table with below structure: create table A_TEST ( order_num int, product_name varchar(50), product_amount int, product_price int ); Sample data : insert into a_test (...
1
vote
2answers
39 views

Filter records and find those with minimal date [closed]

I have the following tables in Microsoft SQL Server: Users: Includes uID (primary key), birthDate, name Conversations: Includes convID (primary key), hostID (foreign key for uID in Users) ...
0
votes
1answer
24 views

PL-SQL “LAST_VALUE” function does not give the desired result

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) and some example data for this table is : col_id ...
0
votes
2answers
20 views

PL-SQL query to find the most recent record the “Operation_date” of which is lower than a specified date

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) some example data for this table is : col_id | ...
6
votes
1answer
77 views

Why is SQL Server spliting it's (JSON) response into multiple rows?

I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
1
vote
0answers
19 views

Evenly select Records on categorical column with Repeating Sequence and pagination in Postgres

Database: Postgres I have a product(id, title, source, ...) table which contains almost 500K records. An example of data is: | Id | title | source | |:---|---------:|:--------:| | 1 | product1 | ...
0
votes
2answers
16 views

MySQL - Get counts of `car models` by `car make`

I have a table with cars data, i.e. Makes, Models, & Year. I want to get the count for each Car with how many models it has... something like the image below: This is the SQL query I am using ...
1
vote
1answer
14 views

A average Mark overall for all of one student subjects

Lets say i have a student table , a enlistment table that shows SubjectId and Results for each student how would i get the overall average mark for a student that has more than 3 subjects Create ...
0
votes
1answer
35 views

How to delete Specific row

http://sqlfiddle.com/#!9/df6245 How to delete all rows Where brand not equal to 107 for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
2
votes
2answers
56 views

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I ...
1
vote
1answer
16 views

Update query using two tables

I want to update the ProductStock field in tblProduct when this query is run. The ProductStock should minus the Quantity field in tblOrderProduct. (ignore the * sign, I just wanted to see if ...
0
votes
1answer
28 views

MYSQL query to return most recent entry as long as it is equal to XXX

OK I'll try and explain this as best as I can as I couldnt really fit it into the title. Let's say I have a database like so: +----------+--------------+---------------+---------+ | Agent | ...
0
votes
1answer
42 views

PostgreSQL vs SQLite [closed]

I was wondering because recently I installed PostgreSQL, and I have previously installed DB Browser for SQLite on my Windows 10 computer. If it PostgreSQL has a DB Browser GUI for the tables, because ...
0
votes
1answer
30 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
0answers
22 views

alternative IN condition on SQL

i have query like this select name, time_attandance from "VIEW_GO_EMP" "viewGoEmp" left join "VIEW_ATTENDANCE" "viewAttendance" on "viewAttendance&...
0
votes
1answer
45 views

Extract ISP name from hostname

I want to get the high-level domain name (not sub-domain) from the hostname. The idea is to "guess" the ISP name from the user's hostname. Ideas? // no ISP when it's an IP 115.20.106.216 =&...
0
votes
2answers
46 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
1answer
15 views

How to get balance of of remaining medicine batch wise

I have this query: select itemcode, batchno, eXpirydate, CASE WHEN type= 1 THEN sum(qty) WHEN type= 2 THEN sum(qty*-1) END AS balance FROM ...
-1
votes
1answer
51 views

Get records where no column is null [duplicate]

I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null. id|f001|f002|f003|....f100 1 |null|aa | ss |sada 2 |sdad|aa | ss |sada 3 |as |aa | ss |...
-3
votes
1answer
49 views

Is there a different (better) way to do this? [closed]

I have one parent table(p) with primary key (id) and two child tables (c1, c2) with foreign keys (pid), i want to return all the records including no matches where id = 1. SELECT p.id, c1.data1, c2....
2
votes
1answer
19 views

SQL daily value (today value - yesterday value)

I have a database where there are sales . +----+-----------+------------+ | ID | Quantity | DATE | +----+-----------+------------+ | 1 | 40 | 2020/10/10 | | 2 | 0 | 2020/10/01 |...
1
vote
2answers
37 views

How to order by COUNT(*) of unique entries in GROUP BY?

I search through a tag_map as SELECT ArticleID,COUNT(*) AS C FROM WordMap WHERE WordID IN( SELECT WordID FROM Words WHERE Word IN ('word1', 'word2') ) GROUP BY ArticleID ORDER BY C DESC I want to ...
0
votes
1answer
49 views

Difficult multi-directional join

I'm being asked to create what (at first) seemed to be a fairly simple/straight forward query. However, I am running into some problems caused by the link relationships between the tables being ...
0
votes
5answers
65 views

How to write search query to retrevie the rows with biggest DateTime <= 2010102248 in SQL

I am creating a table and now trying to create my first Search Query. I have created the table successfully like this: CREATE TABLE testTable ( DateTime INT NOT NULL, /*yyyyddhhmm, ...
-1
votes
2answers
30 views

Help in SQL query for beginners, max of column

I have 2 tables, one holds questions the other holds answers to the questions, each answer has a number of likes, I need to print each question with its most liked answer. I have added a picture to ...
0
votes
1answer
28 views

SQL Query - Pivot with Ranges

This is my sample table BSEG_ID DST_ID BILL_SQ AUDIT_CALC_AMT BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT 12334 REV-WSC 62 6.40667 For 0 - 60 (Next 62.000 cubic ...
0
votes
1answer
38 views

Remove duplicate records with special rules - Is it possible with a single query?

I have the following data in a table: id loc1 loc2 zip_code ------------------------------ 10 1 null 12345 10 null 1 null 11 1 null null 11 null 1 ...
1
vote
1answer
83 views

Dynamically deciding which operator to use based on the type of the value at hand

I'm currently implementing a translation between a proprietary expression language and postgres queries at work. So far a lot of stuff fell in place quite nicely, but now I have trouble with one ...
0
votes
1answer
12 views

How do I select the total count of records over the past week, including days which have no records in MySQL

Essentially, I need a query which can output something like: +------------+-------+ | date | count | +------------+-------+ | 2020-09-30 | 10 | | 2020-09-29 | 3 | | 2020-09-28 | 0 | | ...
0
votes
1answer
23 views

Combine Multiple Rows Results From Left Join in a unique cell

¡Hi guys!, I don't have any idea how to create a new column that contains all information of results from a Left Join if exists the value Example Table 1 -------------------------------------- | ...
0
votes
1answer
35 views

Postgres JSONB - Flatten nested objects and groupings

As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators Now, those functions are perfect for a simple for ...
-1
votes
3answers
49 views

Oracle 11g, show running queries [duplicate]

I've a web application which submits some queries to Oracle 11g. I want to know what are these queries, exactly. I there a way I do this in Oracle 11g? Couldn't do this in the web application itself.
0
votes
1answer
32 views

order by slowing down query with multiple joins and limit/offset on larger result sets

I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on ...
0
votes
1answer
30 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":[{...
-2
votes
1answer
52 views

multi-part identifier could not be bound [closed]

I have 3 tables: table_a needs to communicate with table_c, but has to go to table_b first. Or put differently: table_a refers to table_b which then refers to table_c. The relationship between the ...
0
votes
1answer
41 views

Query returns NULL - Oracle

I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through ...
0
votes
0answers
15 views

SELECT statement optimization MySQL

I am looking for a way how to make my SELECT query even faster than it is now, because I have a feeling it should be possible to make it faster. Here is the query SELECT r.id_customer, ROUND(AVG(tp....
1
vote
0answers
8 views

show an array of the highest to lowest with column it belongs to from another table

I have two tables, one for suppliers_list and the other for purchase_orders. I want a query on the suppliers_list table to get the list of suppliers_id and suppliers_cat with a while loop and another ...
0
votes
0answers
21 views

Mismatched Identifiers in query for columns with same name but different casing

AWS has a service called Config that allows you to use SQL-like syntax to query your resources. For whatever reason, they changed a field that used to be called dnsname to dNSName when going from v1 ...
0
votes
1answer
83 views

PostgreSQL Query output to JSON

I would like to get the output of a particular Query from the postgresql server having joins of a multiple Tables with one to many relations in the json format to a particular file. The Query output ...
0
votes
1answer
23 views

How to make MySQL query performant WHERE one column uses greater than and one column uses less than?

I'd like to make this query perfomant-- SELECT * FROM my_table WHERE foo_id = 8 AND enabled = true AND start_time < '2020-09-12T06:59:59.999Z' AND end_time > '2020-09-07T07:00:00.000Z' This is ...
0
votes
1answer
29 views

Seperate value of a specific column with “,” delimiter in Oracle using “Regular Expression”

I have a table with below structure: create table CUSTOMER_INFO ( cust_id NUMBER, cust_name VARCHAR2(50), cust_address VARCHAR2(50) ) Example Data : cust_id | cust_name | ...
1
vote
2answers
25 views

How to use GROUP_CONCAT for a column and COUNT(*)?

I get the ids of a query by GROUP_CONCAT. SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...) How can I merge this query with a COUNT(*) group by SELECT ArticleID,COUNT(*) FROM TagMap WHERE ...
2
votes
2answers
34 views

How do I stop PG from wasting ids when doing ON CONFLICT DO NOTHING?

I have an empty table with one bigserial id column and one unique text column, and do this: INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test INSERT INTO tab (col) VALUES ($1) ON ...
0
votes
1answer
44 views

Users with highest sum up price

I have a table with name, email, user_id, and price. On this table, a user can appear more than once depending on how many purchases. I am trying to get select all details of users with the highest ...

1
2 3 4 5
20