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).
1,534
questions
0
votes
0
answers
15
views
Querying arrays in JSON fields
The DB is Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0.
Suppose we have a table CASE_ISSUES with a JSON field ISSUES_JSON:
CASE_ID
ISSUES_JSON
105
[{&...
- 115
0
votes
1
answer
30
views
Out of my depth with post mysql 8 update (Warning: #1287 Setting user variables within expressions is deprecated)
Now I can get my way around PHP, and I can get around basic MySQL queries, but I'm not really comfy with MySQL, and have inherited an issue that occurred today after our hosting company updated their ...
- 1
0
votes
1
answer
39
views
Efficient PostgreSQL query syntax for duplicating the result
Assuming I have the following PostgreSQL query:
SELECT (
SELECT SUM (t.customers)
FILTER (
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
...
- 125
0
votes
0
answers
5
views
Power Pivot/Data model issue
Good day,
I am facing two blocking points where I would need your insights please.
I have downloaded all my sheets in power query and made the necessary transformations, including creating a common ...
0
votes
1
answer
26
views
Validate "insert", "update", "delete" in the body of a trigger
I need to create a query where I can remove triggers that have "insert", "update" or "delete" in their body from the result.
I'm using the following query for this
SELECT ...
1
vote
1
answer
16
views
Identify sequences of events starting at well-known start locations, end locations not predetermined, and WITH MYSQL 5.x
I'm working within the limitations of MySQL 5.x.
Several security guards are responsible to walk around a property and visit checkpoints. Two specific gates permit entry and each guard must begin a &...
- 11
2
votes
1
answer
51
views
How to locate a procedure in the body of a trigger
I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body
-- I'm using ...
0
votes
0
answers
24
views
Write SQL query to create output dataset with quarterly exchange rate based on input table with exchange rate by monthly
from_currency month year exchange_rate
GBP 1 2023 0.7952
GBP 2 2023 0.7738
GBP 3 2023 0.7934
GBP 1 2024 0....
- 1
-1
votes
1
answer
19
views
select user with least amount of tiquets between all other users
I have a table where I store users info like this
user
tickets
s.lewis
0
a.barrantes
2
x.mendez
3
j.robert
0
I want to create a query to select the user that has the least amount of tickets ...
0
votes
1
answer
42
views
Inserting Multiple Rows into a Table and Mapping Newly Created IDs Back to Original rownum in Another Table - SQL Server
I have the following tables
-- Create the #product table
CREATE TABLE #product
(
id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
name VARCHAR(100),
createdDate ...
- 439
1
vote
1
answer
41
views
Specific query gives me trouble
I'm supposed to run this query in PGadmin:
SELECT
n2.cpr,
n2.adresseringsnavn,
n2."fødselsdato" AS foedselsdato,
n2.morcpr,
n1.mor_navn,
n2.farcpr,
n3.far_navn
...
-1
votes
1
answer
54
views
Question on joins with multi tables
I have 4 tables ( 1 to many table relation) as shown
Main , Worker , Location and MiddleName Table
Any suggestions on how to get the result. I tried joins and it never worked.
- 1
4
votes
4
answers
2k
views
What's the order of operations in PostgreSQL?
I'm a DB student, and I executed the following query to learn a couple things at the same time (LEFT/RIGHT JOIN, UNION, WHERE + RegEx).
What's troubling me is the order of execution. I have two tables,...
- 59
0
votes
2
answers
53
views
Replace multiple words in a single value using SQL
I have two tables, #customer and #abbr. The #customer table consists of two columns: 'id' and 'name,' while the #abbr table includes entries for abbreviations and their corresponding full forms. My ...
- 439
0
votes
1
answer
44
views
Performance issues with @> operator in postgres 14
CREATE TABLE public.article (
id uuid NOT NULL,
tags _text NULL,
CONSTRAINT article_pkey PRIMARY KEY (id)
);
I have the requirement to load articles that have a tag which is in a large ...
- 25
0
votes
2
answers
102
views
How to apply JOIN only if there are rows available
Given the following database:
CREATE TABLE "Activity"
(
"id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
"designation" TEXT NOT NULL
);
CREATE ...
- 109
0
votes
0
answers
27
views
How to stablish a partitions & indexes stategy in Postgres for 100s of milliion rows
For the purpose of learning, I am playing with Postgress. In particular partitions and indexing.
The Tables
Let's say I have the following tables on my system:
webs
web_id --> BigInt
web_tld --> ...
- 51
0
votes
0
answers
19
views
Keep both non-overlapping intervals and overlapping ones with a larger id MySQL
I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique....
- 101
0
votes
1
answer
24
views
MySQL Data not flushing to disk
Lets start with what I did...
I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries
I copy ...
0
votes
0
answers
24
views
Azure Synapse query to multiple JSON files in data lake, parse data into table format
I want to query RAW telemetry data stored in JSON format in my data lake. I am using Azure Synapse to query the files.
I can successfully pull the data I want into a result table, but I struggle to ...
- 193
-2
votes
1
answer
42
views
I want to assign a character value to a field that contains numbers without using CAST or CONVERT so as to avoid cardinality estimate warnings
I'm looking at something like
SELECT CASE WHEN MyField LIKE [0-9] THEN 'xyz'
WHEN MyField LIKE [10-99] THEN 'abc'
WHEN MyField LIKE [100-999] THEN 'def'
WHEN MyField LIKE [1000-9999] THEN 'ghi'
ELSE [...
- 1
0
votes
0
answers
23
views
SQL Select based on pattern
I have a SQL column in my system that has a bunch of file names. I am attempting to separate the file names based upon a matching file name pattern. Some files have a middle initial and some dont. ...
0
votes
0
answers
46
views
Does Athena have ref cursors?
Does Athena have a Ref Cursor type construct ? Our team members have written pyspark sql in the following format
SELECT dr.case__c as caseId,
collect_list(struct(
dr.id as documentId,
...
- 103
1
vote
0
answers
53
views
Adjust SQL query to select only one record
I have three tables:
An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null).
id
alignent_id
...
- 61
0
votes
1
answer
26
views
Strange query behavior in PostgreSQL
Database is PostgreSQL15.
I am writing a query that will return a list of table partitions I need to trim. My partitions are named as such: table_YYYYDOY, where YYYY is the year and DOY is the Day of ...
- 103
0
votes
0
answers
32
views
Query takes really long
I am running into issues with this query. I am running on a VM, windows server 2019, MSSQL Server 2022, 128 GB of ram, and 8x of these processors:
Processor Intel(R) Xeon(R) CPU E5-2697 v4 @ 2.30GHz,...
- 1
0
votes
0
answers
31
views
How can I duplicate adjacency lists without breaking parent-child relationships?
I would like to duplicate hierarchical structures, modelled as adjacency lists, in my database.
The following tables and records allow a minimal reproduction:
CREATE TABLE item_groups (
id serial ...
- 53
0
votes
1
answer
31
views
Oracle MAX gives different result than ORDER BY desc
When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting:
with some_data as
(
select '123A55' as hex
from dual
union all
...
0
votes
1
answer
38
views
Select distinct
I had some confusion regarding distinct keyword...
For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
-2
votes
1
answer
52
views
Oracle SQL - Selecting First Row for each ID
I am using Oracle SQL Developer and I am trying to pick the first entry of the student population.
So, that I have one row for each ID.
ID
DEGREE
AWARDED
ACADEMIC PERIOD
147
GR
MS
2022
147
UG
BS
...
0
votes
1
answer
41
views
New column that subtacts 2 rows from a different column and pushing out the difference number
I am doing this query, I want to be able to take 2 values from different rows from 1 column(Population growth rate) subtract them from each other, and put that value into the new column(...
- 1
1
vote
4
answers
56
views
Loop through 30 columns in Oracle DB to check if a value exists
I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it.
ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE
...
- 13
0
votes
1
answer
55
views
Order by problem caused by the '_' character
I'm using MSSQL Server 2019 and I've some data like;
| FileName |
|----------------|
| DummyData._0 |
| DummyData._1 |
| DummyData._10 |
| DummyData._100 |
| DummyData._101 |
| DummyData....
- 42
0
votes
0
answers
19
views
Generate list of tables/ views hit by a list of queries in Snowflake
Snowflake provides a list of queries against the databases in account_usage.query_history. I'd like to dive deeper into what tables/ views are being hit most often -- but often there are very ...
- 1,651
0
votes
1
answer
72
views
How to speed up query for stock balance?
I have 2 tables StockIn and StockOut and calculated stock balance (in-out).
Both tables contain millions of records.
When I calculate the stock balance, due to the millions of records, my query ...
- 3
1
vote
1
answer
120
views
How to update multiple records after insert/update in SQL Trigger?
I have below query, which updates timestamp for 10 records.
UPDATE [dbo].[file] SET timestamp = GETDATE() WHERE id <= 10
I created a trigger on that table Insert/Update.
SET ANSI_NULLS ON
GO
SET ...
- 13
1
vote
0
answers
27
views
Possible explanation for why "SELECT" query executed from JDBC returning just the first row in the table
I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code:
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE";
static final String ...
- 111
0
votes
2
answers
75
views
Comparing 2 count columns with case
I have 2 tables, size and cost. My query does a count with a greater than comparison of cost and counts different sizes.
table1: size u
table2: cred t
Here is my query:
select u.size as size
,...
- 25
0
votes
1
answer
38
views
Make a query where the colums are based on finding text from another table
This is probably easier to explain with a toy example.
I have a MSSQL database with two tables. Firstly, I have table A, which has two columns: "Dish_name" and "Dish_ingredients". ...
- 101
-1
votes
2
answers
362
views
PostgreSQL: Issue Declaring Variables in Query
I'm trying to declare a variable for later use in a PostgreSQL query. I've done this often in TSQL, but I wasn't sure about the syntax.
The stuff I've seen online all points to something like this:
...
- 3
2
votes
3
answers
56
views
Find percentage based on values for each ID
I have table1 with 2 ID's and 2 values per ID (Y,N). I can count the values by the following query:
select id
,count(*) as "total"
,choice
from table1
where id in (1,8)
group by id, choice
...
- 25
0
votes
1
answer
45
views
Join 1 row out of multiple 1:1 tables and return as array of json objects
I have a database structure like so (in Postgres):
An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types ...
- 1
0
votes
0
answers
36
views
Faster running for this query sql server
i used database engine tunning advisor for creating some index and statistics but still run slow
SELECT
C.code_commande AS 'Order',
C.code_client AS 'Cust.',
...
0
votes
1
answer
33
views
How to find missing number from different ranges
I have a table with two columns, one containing name and other numbers. For each name there is range of numbers and there are around 250 names in there. Range of every name is different. It may be 1 ...
1
vote
1
answer
55
views
creating several records from one, based on a numerical value stored in a field of the source record
In a PostgreSQL db, I have a table with a numerical field, among others
In such field, some natural numbers are stored and they represent the number of occurrences of something
like this, roughly
...
- 117
0
votes
0
answers
12
views
Find document with regular expressions in collections related to populate in mongoose
The aim is to filter a collection of documents (Requests) based on the document field from another collection (Patients, Clients, Doctors, and Services), of which there are several references in the ...
- 101
0
votes
0
answers
21
views
Rows matched but changed 0
I have been scratching my head for hours.
Query matches but nothing is updated. Mariadb 10.6.
UPDATE field_data_field_coupon_description
SET field_coupon_description_value = 'Limited time only ...
- 13
0
votes
1
answer
187
views
Finding when columns are added- SQL Server
In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
0
votes
0
answers
9
views
Implementing First In / First Out for fuel transactions (such as purchases at varying prices and transfers between locations)
After being tasked with trying to improve the way my company keeps the fuel inventory, i hit a wall. My SQL knowledge is pretty basic and i don't even know where to start.
A little background:
The ...
0
votes
0
answers
22
views
Finding tree contours using nested set model
Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model?
I have described tree ...
- 131