86 questions from the last 30 days
-1
votes
0
answers
41
views
Problem adding argument in the function with other default parameters [closed]
I am using PostgreSQL 18.0 and I have written a function with default parameters which inserts movie into the table. However, when I add the argument which must be added, it gives error:
SQL Error [...
- 21
0
votes
1
answer
60
views
Calculate percent of total per firm and filter firms above 50%
I'm trying to calculate the percentage contribution of each firm to the total amount across all firms.
Database:
google-bigquery
What I need (desired output):
Sum usde_haircut_amt per firm
Compute ...
-2
votes
0
answers
75
views
Oracle: how to filter for the partial date from any string appear in the column? [closed]
I have a case where I need to filter for the partial date string from a column.
Here is the input:
date_value
-----------------------------
7-03 start 4444
03/2022 follow up
1111 7-03 03/2022 ...
- 15
0
votes
1
answer
74
views
How to remove duplicate rows based on array column subset relationship in DolphinDB?
I have a DolphinDB table with an array vector column. I need to remove duplicate rows based on subset relationships within that column.
Sample Input:
sym
prices
a
[3,4,5,6]
a
[3,4,5]
a
[2,4,5,6]
a
[5,...
- 27
0
votes
1
answer
24
views
Redshift PartiQL for unpivoting the keys and values in a Map typed SUPER
AWS documentation for querying Redshift's super is very much all in on supers that are arrays.
But, I happen to want a super that is a map, as I'm sure a number of people do, and I would like to then ...
- 45.6k
1
vote
3
answers
78
views
How to group the data to avoid multiple rows
I have an employee Manager table where you can see for every deptID, there are multiple rows.
A few rows have null employeename and a few rows have null managername for same DeptID. i would like to ...
- 590
-2
votes
1
answer
107
views
How to display in postgresql only the records that are not within the 10 minutes [closed]
For example, I have this table below, how can I display only the records that aren't in the 10 minutes range. In this example should return the first and last in note examples
user_id | ...
- 2,711
-2
votes
2
answers
70
views
Oracle SQL Test to identify missing rows for unique identifier
I am trying to find an Oracle SQL to test whether a table (CONTACTS) contains two rows of data for each primary key (PK) called REF_NO.
The table has a PK called REF_NO and a second PK called CONT_IND....
- 51
0
votes
1
answer
125
views
Make MySQL evaluate subquery first
SELECT tt.trans_type_name AS transaction_type,
trans.transaction_time,
a.trans_action_name AS transaction_action_name,
trans.transaction_notes
FROM(SELECT Cast(...
- 163
1
vote
1
answer
74
views
Creating Materialized view in PG 17 ignores data types in SELECT
I have the following SQL to create a materialized view and the SQL seems reasonable to me but it's unclear from Postgres docs what should happen in this case.
I am listing the columns when creating ...
- 448
1
vote
1
answer
112
views
Postgres Recursive Query Behavior
I have a table test with two columns, each referencing the other
ID1
ID2
2
1
3
2
4
3
5
4
Given an id, I need to traverse the chain to return all the rows traversing the chain. Below is the query I ...
- 325
Advice
2
votes
7
replies
101
views
Splitting SQL output evenly into 3 HTML columns
I am trying to take the output of a SQL query and split it evenly into 3 HTML columns such as flexbox divs. Obviously the output total won't always be evenly divisible by 3 and the first 2 totals ...
0
votes
1
answer
72
views
Why are my data quality validation rules not triggering for null values in my dataset?
I’m working on a data quality workflow where I validate incoming records for null or missing values.
Even when a column clearly contains nulls, my rule doesn’t trigger and the record passes validation....
2
votes
1
answer
115
views
SQL: Calculating percentage of time spent in a country
I have this table in SQL:
CREATE TABLE myt (
name VARCHAR(20),
country VARCHAR(20),
date_arrived DATE,
date_left DATE
);
INSERT INTO myt (name, country, date_arrived, date_left) ...
- 6,845
3
votes
1
answer
92
views
Cause of and means to avoid "flat BNL join" in mariadb 10.11
I'm trying to help a user in another stackoverflow question and have bumped by head into a strange behaviour.
As I rarely use MariaDB, I'm opening another question to investigate the behaviour,
...
- 87.4k
3
votes
3
answers
129
views
How to retrieve a sub-array from result of array_agg?
I have a SQL table in postgres 14 that looks something like this:
f_key
data1
data2
fit
1
{'a1', 'a2'}
null
3
1
{'b1', 'b2'}
{'b3'}
2
2
{'c1', 'c2'}
null
3
Note that data1 and data2 are arrays.
I need ...
- 303
1
vote
4
answers
210
views
Correlated queries without the LATERAL keyword
I am using MariaDB v10.11.15 (a MySQL fork) which does not support the LATERAL keyword, to lookup the countries of 30 IPv4 numbers. I have the following query that retrieves the country when given an ...
- 2,420
Best practices
0
votes
7
replies
154
views
Oracle - how to extract the date from string?
I am trying to find the right pattern which can extract the date from any string
"""
28/11/22 11-23333 to 28/11/22
11-23333 28/11/22 to 28/11/22
something 20.02.2022 end to 20....
- 15
0
votes
2
answers
86
views
SQL attempting to group by multiple columns with "chained" column generation
I'm trying to get SQL to generate a count of rows based on 2 values, from a table like shown below
Table Data:
ID
NAME
VALUE
device_id
1
name_1
enabled
1
2
name_2
enabled
2
3
name_3
enabled
3
4
name_1
...
- 21
0
votes
1
answer
55
views
Sybase ASE avoid permission issues
I want to query multiple tables across databases, something like:
declare @sql varchar(100)
select @sql = 'select * from mydb1..mytable'
select @sql = 'select * from mydb2..mytable'
exec(@sql)
...
- 4,651
0
votes
4
answers
135
views
Complete query in Oracle to find PK name
According to Google this should be the query to find the PK name:
SELECT constraint_name
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME' AND constraint_type = 'P';
However, it does not use ...
- 6,473
2
votes
2
answers
180
views
How to avoid "ON CONFLICT DO UPDATE command cannot affect row a second time" error in WITH statement
I have two tables: demo at db<>fiddle
Table keywords has two columns id and v (which hold the keyword's value)
create table keywords(
id int generated always as identity primary key
,v text ...
- 12.4k
0
votes
0
answers
102
views
GridDB SQL error while using GROUP BY RANGE
I am getting error using GROUP BY RANGE in GridDB sql. I am referring to the example mention in the doc https://griddb.org/docs-en/manuals/GridDB_SQL_Reference.html#group-by-range
name: trend_data1
ts
...
- 73
5
votes
4
answers
206
views
UPDATE with LEFT JOIN and condition IS NULL
I have the following table definitions:
Table public.messages:
Column
Type
Collation
Nullable
Default
ip
text
msg
text
ignore
boolean
Table public.host:
Column
Type
Collation
Nullable
Default
ip
text
...
-4
votes
0
answers
99
views
How to reset cumulative sum when a value changes sign [closed]
I'm calculating a conditional cumulative sum in DolphinDB SQL that resets the accumulator when the value changes sign (from 1 to -1 or -1 to 1). Here's a complete, reproducible scenario and my ...
- 1