84 questions from the last 30 days
5
votes
4
answers
220
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
...
Advice
0
votes
9
replies
141
views
How to make it so a parent NEEDS its children to exist in MySQL?
I am designing a DB where basically parent can't exist without two children. E.g: Entity set Marriage, CAN'T exist without entity set Man and entity set Woman, but neither Man or Woman need Marriage ...
- 21
5
votes
3
answers
166
views
Regex function to extract numbers from string-type values, separated by "-", to get the difference of each two values
I'm 100% new to Regex so I've been floundering about on regex101 trying to figure out how to get my desired output. I am using PostgreSQL to write a query to extract a set of values from the string. ...
4
votes
4
answers
239
views
Sort aggregated query results by two methods simultaneously
I need to sort a query's results by two methods at the same time.
I want the first 3 records (returned) to be based on their prevalence in another table
And then I want the rest of the results sorted ...
- 443
Best practices
0
votes
7
replies
94
views
MySQL SELECT … FOR UPDATE causing table lock during high traffic while generating sequential transaction IDs
I have a PHP + MySQL project where I need to generate sequential transaction IDs.
Each transaction can have multiple items, so all rows for that transaction must share the same txn_in value.
Tables
...
3
votes
2
answers
152
views
'EXEC' is not processing all of submitted SQL string [closed]
I am creating a varchar(max) string for submission to 'EXEC' and it is less than 1000 bytes long, yet the 'exec' errors out - seems to not get the entire string.
Below is the code that errors out:
...
- 53
Best practices
0
votes
6
replies
143
views
MySQL query to calculate based on 2 sets of data
I have a MySQL database that I have the following type of data records. It's a little more in depth than this but its good enough for the sample question.
Ticket Number
Event (Start or Stop)
Location
...
- 331
Best practices
0
votes
7
replies
161
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....
- 11
Advice
2
votes
7
replies
113
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 ...
Best practices
0
votes
8
replies
141
views
Should I generally use a LIMIT clause when testing my SQL queries to a database?
I am learning SQL from a class online.
I wanted to ask if it's generally best to use LIMIT in my queries to make them run faster, or at least until I have them tested and pulling the right data.
If ...
3
votes
3
answers
147
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
2
votes
2
answers
199
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
6
votes
1
answer
140
views
Calculate a running highest streak
I am working on a gaps & islands problem in MS SQL Server. I'm doing a daily challenge, and the table is a list of days I accomplished the task and the challenge day (calendar days since the first ...
Advice
0
votes
6
replies
159
views
Pyspark SQL: How to do GROUP BY with specific WHERE condition
So I am doing some SQL aggregation transformations of a dataset and there is a certain condition that I would like to do, but not sure how.
Here is a basic code block:
le_test = spark.sql(""&...
- 21
Advice
0
votes
3
replies
70
views
Procedure to show percentage of nulls in TERADATA SQL ASSISTANT
I'm working in Teradata SQL Assistant and I need to create a stored procedure that, given a table name, returns the percentage of NULL values for each column in that table.
Right now, I generate the ...
- 67