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
vote
1answer
664 views

InfluxDB: selecting last() values from different tags

I have a bucket with the raw data of several sensors, things like temperature, air pressure and etc. The sensors don't send their data at the same time, which means that a given timestamp might have ...
1
vote
2answers
30 views

MySQL: Updating large data set including time aggregation

I am having a large MySQL (5.7) table with millions of rows (contains data for each second). Based on these values calculations should be performed, in some cases for large data ranges. Therefore, I ...
0
votes
2answers
31 views

How to update the time of each row and check if its updated or not

Name Time Status T1 9/12/2020 12:12:09 expected T2 8/06/2020 11:09:12 expected These are table name and updated time in the DB I want to get the status and time of updation of specific tables .Set ...
0
votes
1answer
85 views

mssql PERCENTILE_CONT group by year

I am trying to prepare a dataset for box chart which requires MAX, MIN, MEDIAN and quartiles grouped by years. This is my attempt, however I could not group it by years: SELECT years, MAX(...
1
vote
2answers
357 views

Query to find all dates between two specified dates using plsql

I have a table with below structure create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
-3
votes
1answer
55 views

Change Consecutive Row Value [closed]

I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd ...
1
vote
1answer
67 views

Query to find the "Number of days" a customer has an active card between a specified interval

I have a table with below structure : create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
-1
votes
1answer
24 views

Job Opportunity and difficulty (or not) for me to learn to use msaccess and surveygizmo/alchemer [closed]

First, if my question fits better elsewhere, please advise. My question is: Based on your experience with relational databases like MSaccess and perhaps survey tools like Alchemer (formerly Survey ...
0
votes
1answer
86 views

How do I join a parent table to two child tables without duplication?

parent table id city_name zip_code 1 new york city 10001 2 los angeles 90001 3 chicago 60007 Customer, child table id customer_name city_id address 1 alice 1 428 Hartford Lane 2 betty 1 249 ...
1
vote
3answers
1k views

Select all rows with same ID when the last record of that ID meets the criteria

I need help with a SQL query. I want to return all rows when the last record (date) of the ID match the criteria. Here is an example : Let's call this table "Test" ID date status 1 10/01/...
-1
votes
1answer
34 views

How to get distinct count on sub table joined via two different way

I have 3 below tables in my db CREATE TABLE main_table ( id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL ); CREATE TABLE middle_table ( id SERIAL PRIMARY KEY, name ...
0
votes
1answer
59 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 | ...
0
votes
1answer
30 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'), (...
0
votes
1answer
39 views

Joining without a unique identifier

Payments PaymentRef Product PaymentAmount MerchantRef P01 ABC 100 MR01 P02 ABC 200 MR02 P03 XYZ 200 MR03 Refunds ReundRef Product PaymentAmount MerchantRef R01 ABC -10 MR04 R02 ABC -100 MR05 ...
1
vote
2answers
40 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
22 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) ...
-1
votes
1answer
29 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
59 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
63 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
68 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
284 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
41 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
1answer
38 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, ...
0
votes
3answers
89 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
253 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 ...
-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 ...
1
vote
1answer
423 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, ...
-1
votes
1answer
39 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 ...
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 ...
2
votes
1answer
38 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
0answers
236 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
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
1answer
488 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 ...
-1
votes
3answers
519 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
82 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
30 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
117 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
47 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 ...
-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 ...
-2
votes
2answers
2k 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 ...
1
vote
2answers
52 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
47 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
188 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
26 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 ========...

1
3 4
5
6 7
25