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
0
votes
2answers
84 views

JOIN and GROUP BY with additional columns

If I run the following query on my database I get the results pasted below: SELECT t.id as ID, j.text as Text, l.location as Location, FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (...
0
votes
1answer
24 views

Postgres handle case to continue Insert other row if some row doesn't exists

I am trying to insert data to a table using below query: INSERT INTO projects_users (user_id, project_id, is_admin) VALUES ( (Select id from users where username='example'), (...
1
vote
2answers
30 views

Use filters instead of queries in MS Access? [closed]

I realize many things could be done in Access like instead of using queries for filtering things, it is posible to use filters. I'm not sure if the program makes SQL with VBA queries under the hood or ...
0
votes
2answers
21 views

Update field with the longest varchar field

I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query: SELECT TOP(1) options FROM ...
0
votes
1answer
21 views

Convert query for count and updated date

I currently run the below query in Snowflake for some of my reconciliations and have never worked in PostgreSQL, which we just adopted. Select count(1), min (LAST_UPDATED_DATE), max(LAST_UPDATED_DATE) ...
0
votes
1answer
37 views

Query to list all the databases that have a specific user

I need to form a query that lists all the databases that include the words 'PRD' that have a single specific user name 'user_1' So far I have this query: select name as username, create_date, ...
-1
votes
1answer
26 views

What does "player != ANY (SELECT player FROM Member)" return?

I have this table and these queries I've run both queries with these results: I'm confused about the line with the WHERE clause in each query. Specifically that ALL and that ANY. I understand what ...
0
votes
2answers
46 views

mysql - adding alias columns as a new column

I have the following mysql query... select x.timest, max(case when x.devloc='outside' then x.value end) as outside, max(case when x.devloc='hvac_main_return' then x.value end) as ...
2
votes
2answers
59 views

Select all rows of all user logins, where user had logged in from more than 3 ip address within the past month

Our SQL Server UserLogins table has these columns: UserID, DateTime, IpAddress. How can I select rows of users who have logged in from more than three different ip addresses within a specified time ...
0
votes
1answer
64 views

MySQL - GROUP BY with JOIN query

I am using MySQL 8.0.23 I have 4 tables: houses, intervals, discounts, and availability. Houses: create table houses ( id int auto_increment primary key, name varchar(255) null, ...
0
votes
1answer
206 views

How do I make PostgreSQL include all the months where I did not buy anything? [duplicate]

I have this query: SELECT avg(sum) FROM ( SELECT date_trunc('month', "timestamp"), sum(amount) as sum FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%' GROUP BY date_trunc(...
0
votes
0answers
39 views

SQL YTD calculations adding periods without postings

I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO ...
0
votes
1answer
11 views

Counting number of occurrences, how to "group by"

I have the following query, which works as intended. Database is Sybase SQLAnywhere, which follows t-sql syntax for the most part. I'm trying to add a column to COUNT the number of times each "...
0
votes
3answers
54 views

Get three last movements of the per ID with LAG and GROUP BY in MySQL

I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements. So I tried grouping by the id ...
3
votes
1answer
170 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
1
vote
1answer
290 views

How to model dependent categories (parent-child-relation)?

Idea / Goal I have two tables: Article (represents a Product) and Category. The relation is like this: An Article can be assigned to only one Category but a Category may have at least one Articles, ...
-4
votes
1answer
39 views

Query Optimization for Subquery

Please help to optimize below MySQL query SELECT * FROM ( SELECT u.name,u.avatar,u.occupation, (SELECT GROUP_CONCAT(revenue)FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS ...
0
votes
2answers
52 views

Vertical Elastic Query is not identifying Object

I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just ...
-1
votes
1answer
35 views

if multiple users run a query that creates a temp table at the same time, would the data in the table be corrupted in each session? [closed]

I am using postgres12. I have a query that creates a temp table named table_x and returns it. I am wondering if this data will be corrupted since there is multiple users running the same query at the ...
0
votes
0answers
43 views

Query to find the second highest row in a subquery

The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system. This is the simplified query that I'm ...
2
votes
1answer
31 views

Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL

I am currently working on a small presentation showing when postgresql will switch from index scan to bitmap scan to sequence scan. First example (hint all index scan…): Loosely based on a similar ...
0
votes
1answer
13 views

Two diferent time mysql databases with same uniqueid autoincrement values need to be merged

I am a beginner at SQL, so I will try to ask for help. As I have a database where is primary key where values are incremented by +1. And I made this database backup, and have another database same as ...
0
votes
0answers
143 views

MySQL compare multiple values in WHERE clause, find subset

I have a table segments with column comp_id, segment_id. comp_id: 1 segment_id: 23,11,223,3331; comp_id: 2 segment_id: 231,11,223,3331; I have another table: comps with segment_id as well. comp_id: 1 ...
0
votes
1answer
326 views

how to get the difference between 2 tables in 2 different schema's in Oracle? [duplicate]

I am using Oracle 11G. I have a table called Tickets in the schema called 'tickets1" I have the same table structure in another schema called 'tickets2' I want to find out What data is the same ...
0
votes
1answer
29 views

Trouble in querying data

I'm having difficulty in retrieving data from tables. Bellow is the scenario I'm stuck at. TableA Id Name 1 ABC 2 DEF 3 GHI 4 JKL TableB Id TableA_Id Tag 1 1 X 2 2 Y 3 0 Z TableC Id ...
0
votes
1answer
16k views

MongoDB - Mongoose query findOneAndUpdate() doesn't update/duplicates the DB

I am trying to save and update (upsert: true - creates the object if it doesn't exist) the result of the Web Api which contains multiple arrays of data to populate the stock chart. Each time I enter a ...
-1
votes
3answers
301 views

Is there really no SIMPLE way to achieve "recursive lookup" in PostgreSQL?

I have a table of categories: id,label,parent_id 1,Animals,null 2,Dogs,1 3,Cats,1 4,Black,3 This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
0
votes
2answers
43 views

Max of a query which is inside

Create a database for a drone delivery startup that distributes pizzas via drones. The startup has partnerships with several drone manufacturers and pizza shops. The entities of interest to the ...
0
votes
1answer
24 views

4 operations with Postrgres

(select price from product_price where product_id=1)/(select price from comp_product_price where comp_product_id =1) I'm holding the current price in the product price and the competitor product ...
1
vote
2answers
68 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 ...
0
votes
1answer
26 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 ...
2
votes
1answer
79 views

Postgres random text in JSONB column

Following is the query I'm using to scrub some fields in the JSONB column. I'm trying to radomize the first and last name so would like to use something like md5(random()::text) as values. update ...
0
votes
1answer
37 views

Get the CreatedAt Timestamp when the number of rows is equal to some number

I have a simple table in SQL Server. id OtherId createdAt 1 A 2019-11-28 05:03:03.983 2 A 2019-11-28 05:03:06.937 3 B 2019-11-28 05:03:12.983 I want to retrieve a timestamp when the number of ...
0
votes
1answer
35 views

Insert mysql query results into database column with corrensponding ids

I'm trying to store some data from a temporary query into a table, but it doesn't work. Suppose we have the following data Table_A Submission_id Salesman Customer name Hobby Carline Fuel ...
-2
votes
2answers
1k views

MySQL Join two tables with comma separated values

I've got two tables as below: CARLINE option_value option_name tipologia_auto_1 Citycar tipologia_auto_2 Berlina tipologia_auto_3 SUV Hobby_1 football Hobby_2 Basket Datimarketing (is the ...
2
votes
3answers
256 views

Query to update one column of a table based on a column of a different table

I have two tables with below structure : Create table Customer_info (Customer_num varchar(50), Customer_Branch int); Create table Customer_Transaction_Info (Customer_num varchar(50), ...
-1
votes
1answer
49 views

Split data across multiple rows in a single statement

I have data in table like Customer Invoice No Date Value Paid Balance ABC 1 01/12/2020 25 0 25 ABC 2 01/12/2020 50 0 50 XYZ 3 02/12/2020 200 0 200 XYZ 4 04/12/2020 100 0 100 ABC 5 04/12/2020 500 ...
1
vote
2answers
46 views

Min function returning too many rows

Apologies in advance if I am asking in the wrong forum. I am trying to get the first attribute value for each ID in my table based on the ID's starting week. When using the min() function on weekkey, ...
0
votes
1answer
44 views

Query to select cards and transactions

I have some trouble with a query definition. My database is about operations in a bank and I want to show the card numbers which were used in transactions at all ATMs. This is how my tables DDL: ...
1
vote
1answer
131 views

how to limit amount of rows in jsonb_agg & jsonb_build_object

I have this query: SELECT jsonb_agg(jsonb_build_object('me_id', me_id)) from message and I get the following result: However I want to limit the amount of objects in the array to a certain number ...
0
votes
1answer
23 views

Procedure with "bind varibale" does not return the desired result set

I have a table with below structure: create table CUST_MODERN_CHANNEL_BRANCH ( customer_Num NUMBER, channel_Code NUMBER(5) ) Some example data : Customer_Num Channel_Code ========...
2
votes
2answers
1k views

NOWAIT equivalent in MySQL 5.7

MySQL 8.0 has a new feature to allow a transaction to timeout immediately by throwing an error if it tried to access records that are locked by another transaction instead of waiting for that ...
0
votes
2answers
29 views

how to select records from two columns where one column consist word from second column?

In postgres I want to select rows where column1 matches column2 for corresponding row and returns result having same values as well as records consisting word from column1. select * from table_name ...
1
vote
1answer
306 views

Receiving error "invalid identifier" while trying to update a table in Oracle

I'm trying to transform a Merge query into an Update query for getting better performance but I could not succeed. Tables structure: create table TEST_FILE_MERGE ( customer_num NUMBER, ...
0
votes
2answers
80 views

How to retrieve missing values of an id from a list?

have a data structure that is value/tag inside a table on Oracle sql Lets say I have this structore exTable ID TAG VALUE 1 1 x 1 4 y 2 2 x 2 3 y 2 4 z I want to retrieve the missing tags for a ...
0
votes
0answers
141 views

Can the Atlas search filter in MongoDB cloud be used to query between dates?

All the examples I read online seem to refer to using the compass or the command line, not MongoDB cloud. I try emulate the same query code in the search filter but not having any luck. For example ...
0
votes
1answer
53 views

Query to get current column based on date range

I have a scenario with the following tables. The issue is when a car is booked in table tBooking it is due to following scenarios. Example User books a car in branch (branch is a location of company ...
0
votes
1answer
34 views

Unusual behaviour of a query when using case expression in group by, some records are being missed

I have 4 table with below structure: Table "Customer_Info" create table Customer_Info ( Customer_Number int, Customer_Issued_Branch int, <Other Columns related to customer&...
1
vote
1answer
406 views

How can I transpose multiple unrelated columns into rows in SQL Server?

I have a table that looks like the following: PersonId EyeColor HairColor FavoriteTVShow FavoriteMovie FavoriteFood BirthInfo ---------- ----------- ------------ ------------------- ...
2
votes
1answer
103 views

how to use the keyword `VALUES` in an `IN` statement?

I am on sql server 2016 select @@version Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-...

1
3 4
5
6 7
24