674,428 questions
1
vote
1
answer
50
views
Equipment inventory tracking - filter out duplicates resulting from equipment marked 'vacant', or best way to mark equipment vacant
I have an Access database that is tracking trucks. I have one table for trucks and all their details, and another table tracking the drivers and their details. The third table is tracking positions ...
- 11
-3
votes
0
answers
51
views
[Oracle][SQL] Optimisation [closed]
I'm working on Oracle 19 and I need to sort a 70GB table.
I have unlimited parallelism.
What would be the optimal values to optimize this sort?
in terms of parallelism
in terms of database ...
- 736
Best practices
0
votes
3
replies
50
views
Feedback on ERD normalization for school project for a service to match users based their preferences, and how to model verification status in ERD?
What is feedback on the normalisation of my ER diagram which I designed for my school project for a service to match users based on their preferences?
The ERD is supposed to show no transitive ...
-3
votes
0
answers
71
views
Azure SQL database TOP n Issues [closed]
I have a dev database with dtu model under s3 tier and uat db with dtu model under s7 tier in Azure SQL databases.
The issue is: I have a stored procedure which runs differently in dev and uat. It's ...
-4
votes
0
answers
46
views
Azure SQL database performance issues [closed]
Query latency differs across environments (Dev vs UAT) – same stored procedure, different performance.
I'm facing a performance issue where the same stored procedure and identical query logic behave ...
-5
votes
0
answers
66
views
Why for altering tables, to add column we keyed in "ADD *Column name*" but to remove column we need to add the word "COLUMN" before the *Column name* [closed]
For example:
ALTER TABLE tbl_student
ADD student_email VARCHAR(20);
ALTER TABLE tbl_student
DROP COLUMN student_email;
Why add column, doesn't require the word COLUMN but drop requires it?
-1
votes
0
answers
66
views
Leading blank line in the result set of XMLAGG function [closed]
I've aggregated multiple values of Item field using the XMLAGG function, as it was exceeding the 4000 char limit. But in the result set it is adding a new line in the first row then aggregates data in ...
- 11
-4
votes
0
answers
83
views
Creating a median function in SQL [closed]
I have a table:
CREATE TABLE items
(
item_date DATE,
item VARCHAR(50),
price DECIMAL(10,2)
);
INSERT INTO items (item_date, item, price) VALUES ('2020-01-01', 'APPLE', 1.50);
INSERT INTO ...
- 6,889
1
vote
1
answer
129
views
SQL fails when adding ORDER BY clause
In Access 2016, I have 3 related tables:
Series
tblSeasons
tblEpisodes
Trying to write SQL to get a list of Series with a number of conditions.
I have found that the following works perfectly fine.
...
- 43
0
votes
3
answers
133
views
How to empty a single column in MySQL without updating each row individually? [closed]
I have a large MySQL table in phpMyAdmin, and I want to clear all data in a specific column.
I know how to do it using:
UPDATE table_name SET column_name = NULL;
Or by dropping and re-adding the ...
- 35
Advice
0
votes
2
replies
28
views
Parallelized Access to TypedTuplestore data structure in PostgreSQL
I am working on a C-UDF for Postgres, where i would like to somehow read from a passed Typed-Tuplestore in parallel. Either pthreads or worker-processes is fine, as long as the access isn't combined ...
- 673
-3
votes
2
answers
118
views
Lookup multiples values from another table [closed]
I have 2 tables
Table1:
ID
rule_id_1
rule_id_2
1
12345
67889
2
54321
12345
Table2:
rule_id
rule_name
12345
Error Detected
54321
Job Completed
Goal is to have a table like this:
ID
rule_id_1
...
- 13
0
votes
0
answers
40
views
Unexpected Floating-Point Precision Error with SUM Aggregation Function in Apache IoTDB? [duplicate]
While using the aggregation functions in Apache IoTDB (version 2.0.5, tree model), I noticed that the result of the SUM function does not match the expected value, showing a noticeable floating-point ...
- 33
3
votes
0
answers
140
views
How optimize SQL query with location search in large radius
I have next PostrgreSQL query:
SELECT *
FROM person
WHERE birth_date between ? and ?
and gender = ?
and ST_DWithin(location, ST_MakePoint(?, ?), 20000)
and activity_date <= ?
and id < ?
...
- 51
0
votes
1
answer
89
views
CASE Expression in JOIN [duplicate]
I am having some trouble with a CASE expression inside a JOIN.
Essentially, what I would like to do, is whenever a FormLookupId = 31 I want to join on any record with the a specific string in the rl....
- 561
2
votes
3
answers
88
views
insert multiple records for multiple tables in a single query
I've got this situation in which I need to copy and split account information:
-- old
create table old_account (
id bigserial not null primary key,
uid varchar(32) -- , etc.
);
insert into ...
- 3,196
-4
votes
0
answers
49
views
JSON explosion and correct grain comparison [closed]
I need to flatten the JSON below in a way that will follow the logic:
1 actions can be repeated
1 personId repeated
however per 1 personId will go unique feeType or correspondenceValue.
also, ...
-3
votes
1
answer
97
views
Group Transaction in To/From columns [closed]
I have this table here as example:
Group
Where
Bill
Amount
A
from
telephone
-300
A
from
internet
-200
A
to
electricity
500
B
from
water
-1000
B
to
cable
500
B
to
gas
500
When you query Group A. The ...
- 9
Tooling
0
votes
6
replies
85
views
SQL vs R : when to use which?
I am in the process of becoming a data analyst and I am having a hard time understanding when to use SQL and when to use R. I have tried re-watching videos of my data analyst course, but I am still ...
1
vote
1
answer
92
views
Oracle: Character Set Mismatch
can you tell me why i get Character Set Mismatch in the first insert even if "in theory" they are equivalent?
Oracle version: 19
SD.ValueText is NVARCHAR2
SELECT value FROM ...
- 55
-6
votes
0
answers
79
views
Merge execution failure [closed]
CREATE TABLE IF NOT EXISTS abcatedt (
abe_name char(30) NOT NULL
, abe_edit char(254)
, abe_type smallint
, abe_cntr integer
, abe_seqn smallint NOT NULL
, abe_flag integer
...
- 6,492
-3
votes
1
answer
66
views
Find Member COE based on service dates [closed]
I have a table with a list of claims for members that have specific service date ranges. The other table has a table of COE descriptions. The COE changes for some members and is indicated by the ...
- 5
Advice
1
vote
9
replies
65
views
Does SQL window function ORDER BY inherit from parent query ORDER BY?
In SQL, if I omit the ORDER BY clause from a window function's OVER() clause, will it use the parent statement's ORDER BY instead?
For example, are these two queries equivalent?
-- Query 1: ORDER BY ...
- 5,080
0
votes
0
answers
57
views
How to handle LONGBLOB-type fields in gbase database?
There is a LONGBLOB-type field in a table that actually stores JSON-formatted data (as shown below).
Column Name
Data Type
user_id
INT
id
INT
harbour_name
VARCHAR
name
VARCHAR
type
VARCHAR
properties
...
- 1
0
votes
2
answers
73
views
Aliasing to prevent ambiguous columns in SQL join
I have a query of the form:
SELECT T1.id, otherT1Column, someT2Column
FROM T1
JOIN T2 ON T1.externalId = T2.id
WHERE <some conditions>
(which is constructed programmatically with JDBC, not ...
-2
votes
0
answers
35
views
MySQL Stored Procedure INSERT INTO a temp table from CTE erroring [duplicate]
I am creating a temp table in a stored procedure to hold the results of a CTE query. Without the temp table the query works fine. The code is below. I keep getting an error at the INSERT INTO line - ...
- 1
0
votes
1
answer
79
views
How to create siblings and children in SQL when using FOR XML?
As I understand it, there are 4 different options when using FOR XML in a SQL query: RAW, AUTO, EXPLICIT, and PATH. I think for my example below, I want to largely use PATH, but I end up getting ...
- 31
0
votes
1
answer
176
views
INSERT unique incremented number from SELECT on the same table by several users without creating duplicates
I have a requirement to generate unique incrementing serial numbers by product type from a VB.NET application using SQL Server Express.
I have created a table and also have a VB form where I am ...
- 1
1
vote
0
answers
84
views
Is it possible to add additional conditions to SQL queries using EclipseLink's built-in tools?
Our project uses the PostgreSQL database, managed via EclipseLink.
Horizontal scaling is planned by migrating to the sharded Postgres Pro Shardman.
Currently, the primary key of each table is a unique ...
- 85
-6
votes
0
answers
79
views
Oracle Merge query failure [closed]
SQL> MERGE INTO abcatedt t USING (SELECT '###-##-####' AS abe_name, 1 AS abe_seqn FROM DUAL) s ON( t.col1 = s.col1 AND s.col2 = t.col2 ) WHEN NOT MATCHED THEN INSERT INTO abcatedt ...
- 6,492
0
votes
0
answers
52
views
ODPS / MaxCompute SQL: What would be the correct joining query when some records return null? [closed]
I’m working with ODPS / MaxCompute SQL on a data pipeline where I need to populate a missing identifier (biz_order_id) in a main table by joining to a reference table.
Context
The main table contains ...
- 17
0
votes
1
answer
53
views
totalRecords count seems incorrect
My Flask application exposes a REST API for CVE data stored in SQLite. The API supports filtering and pagination, but is my totalRecords count logic correct when filters are applied?
totalRecords ...
- 1
0
votes
0
answers
39
views
How to calculate the rate of change between current point and the 10th previous point in Apache IoTDB?
I'm using Apache IoTDB 2.0.5 with tree model to process time series data, and I need to calculate the rate of change between each data point and its 10th previous point (i.e., (current value - 10th ...
-1
votes
4
answers
151
views
SQL Query to Filter Rows Above Average While Removing Consecutive Qualifying Rows
I have a data table with a column A. I need to filter rows where the value in column A is greater than or equal to the overall average of column A. However, when two or more consecutive rows meet this ...
- 21
Best practices
0
votes
4
replies
38
views
Postgres: Best design pattern for "Exclusive Arc" (Polymorphic) relationships? (Nullable FKs vs. alternatives)
I am designing a database schema in PostgreSQL for a notification system. I have several distinct "parent" entities that can trigger a notification log:
order
invoice
shipment
...
0
votes
1
answer
42
views
Inaccurate Results from Apache IoTDB count() with WHERE Condition on Boolean Series
I am using Apache IoTDB 2.0.5 in the tree model and want to count the number of false states in a sequence using the count function, but the returned results have errors.
The raw data is as follows:
...
- 1
0
votes
1
answer
131
views
Update MS Access db with SQL that includes select statement
I'm using NodeJS and node-adodb to try to run an update on a MS Access database that includes selects. I tried this:
update [Objects]
set
[Object] = 1,
[CardID] = (select id from Oracle where ...
- 3,292
4
votes
1
answer
119
views
Is ORDER BY inside an Oracle view preserved when I query the view?
Let's say I have a simple Oracle view with an ORDER BY clause inside it.
create or replace view MY_VIEW as
select
*
from
MY_BOOKS
order by
TITLE;
Let's say I run a simple query select * from ...
- 733
2
votes
3
answers
159
views
Get SQL result with previous and next row
I want to add to my result rows the immediate previous and following row according to a specific clause. Simplified case:
ROWID
value
x
level
0
1.0
0
1
1
2.0
1
2
2
3.0
2
1
3
4.0
3
2
4
5.0
4
1
5
6.0
5
...
- 7,731
-3
votes
2
answers
105
views
Select rows with defined value + anything else [closed]
Employee
name
lang
Nick
C#
Nick
SQL
Eva
Rust
Vi
SQL
Iv
Java
Need to select employees who know SQLr + something else
i.e.
Nick C#
Nick SQL
My attempts are:
1.
select e.name
from (
select name
...
- 3,233
Best practices
0
votes
15
replies
120
views
Any advice on Inner Join query performance
I have a T-SQL query shown here which is taking forever to run.
I am trying to see if there is a better way to write the Inner Join statement. The source table has a millions of rows.
Is there any ...
- 1,334
1
vote
2
answers
150
views
SQL column from different tables to array
I have multiple functions to convert column values into an array
CREATE FUNCTION FN_ToArray
(
@separator NVARCHAR(10) = '|'
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @return VARCHAR(500)
SET ...
- 117
3
votes
2
answers
84
views
Grouping after sorting
From this data, sorted on num,
|num |fruit
|----|------
|1 |apple
|5 |apple
-> edit: there could be n apple rows here.
|6 |banana
|7 |apple
How can I get this result:
...
- 521
0
votes
2
answers
106
views
Incorrect ordering in CTE query
I run the below query against a view in MS Fabric environment (Data Warehouse). Here the compl_officer_id is the same across management_id partition (unfortunately there are no other unique values ...
- 65
3
votes
0
answers
98
views
Spark Parquet timestamp min/max statistics
I have a table in Iceberg as below:
spark.sql("""
CREATE OR REPLACE TABLE my_db.my_table (
serverTime TIMESTAMP,
id LONG,
...
)
...
- 31
Advice
0
votes
4
replies
60
views
BigQuery Standard SQL: UPDATE to convert incorrect 0 values to NULL returns an error
I’m using Google BigQuery (Standard SQL) to clean 2023 weather data. Missing values were mistakenly stored as 0, but 0 is a valid value for some rows, so I only want to convert the incorrect zeroes to ...
-1
votes
2
answers
128
views
Join two table with column values based on condition being met [closed]
I am trying to flatten out two different tables for a report(view) but I am having a hard time. One table is tbRecord which contains a unique master record id and a sub table tbInspectedPart that ...
- 587
0
votes
3
answers
176
views
Trouble with SQL Server query containing DISTINCT, ORDER BY and FOR XML PATH commands [closed]
Here is an extract of table B_TimeEntryMatter.
ProformaNr
ActivDate
Description
451894
17/12/2025
To the provision of a Certificate of Incumbency by the Secretary;
451894
18/12/2025
To time spent in ...
- 21
Advice
0
votes
19
replies
75
views
Get a single data entry from a custom database table in WordPress?
I added a custom table to my database and I want to call on a particular row as defined by the ID and then display the string contents of another column in that row.
The user makes a selection from a ...
- 39
1
vote
2
answers
71
views
Calculating a Percent with a Filter on Text Dates [closed]
I've consulted a few threads on how to calculate percent and I am not sure why but I do not get the same result as I expect to. This is the closest to my actual value:
select round(
(100.0 *
...
