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,590 questions
2
votes
2
answers
248
views
Variable SQL join operator using case statement
I have a fact table that contains sick leave values for employees (Table 1) and I need to slot each value into one of the bands (Table 2)
The problem is that the fact table contains some negative ...
- 23
0
votes
2
answers
51
views
Guarantee SQL Management Studio Query
When running a query in SQL Server Management Studio, is it then possible to guarantee that the script will only target the designated database?
When I select a database, that's fine and good and all -...
1
vote
2
answers
54
views
Sql Server: Query to parse and validate codes
We have a #ValidCode table with list of valid codes like: 'A', 'B', 'C', etc. Another table called #SourceData with input data -- that comes as a combination of valid and invalid tokens (sometimes ...
- 707
2
votes
3
answers
290
views
Deleting multiples that aren't exact PostgreSQL
I think the issue came when inserting my data, some of the data looks like this:
id
year
1
2000
1
2001
1
2002
2
2000
2
2001
2
2002
3
2000
3
2001
3
2002
Where the intention was for it to ...
1
vote
0
answers
28
views
Having issue with YOY query
I have following YOY query, works fine but the vendors which are not common is missing from the final result, can anyone please guide on what am I doing wrong?
SELECT `current_period`.`vendor_name` AS ...
- 111
5
votes
1
answer
874
views
Query to delete records with lower eff_date in a large table with 400 million records
I have a table with below structure:
create table TEST_TAB
(
activity_type CHAR(1),
tracking_code NUMBER,
eff_date DATE
)
Sample data for this table:
insert into TEST_TAB (activity_type, ...
- 1,486
0
votes
1
answer
68
views
how to get the location of the backup too? you can use multiple columns pivoting or any other way!
I got this great query that shows the latest backup for each existing database.
SELECT
M.name,
[Recovery Model] =
M.recovery_model_desc,
[State] =
M.state_desc,
[Last ...
- 16.6k
0
votes
0
answers
47
views
Postgresql database went from 56 Gb to 185 using one query
we are using Pgadmin 15 to manage our database and yesterday, we launched a basic sql query. I waited for 10 min and the result was a bit strange. I got the message "sql state : 53100" which ...
0
votes
1
answer
32
views
Mysql : how to join 2 row different colum using same table?
I try to get output from 2 row in single row for attendance report, but i dont know how. please help me
Table : attendance
no
id
punch_in
punch_out
1
1
2024-10-04 08:00
2
2
2024-10-05 08:10
3
2
...
- 1
2
votes
1
answer
78
views
UNNEST vs ANY()
When selecting a number of records from a Postgres table based on a list of ids passed as a parameter, what are the benefits and drawbacks of
SELECT id, <more...>
FROM tbl
INNER JOIN UNNEST($1) ...
- 123
0
votes
1
answer
64
views
slow postgres 10 query - have I done something stupid with my indexes?
I have a database with a large (50M) table of time series observations and locations (900 rows). The following query takes quite some time:
SELECT *
FROM observations
WHERE location IN (SELECT ...
- 101
0
votes
2
answers
38
views
How do I use EXPLAIN to identify next step in query optimization?
I've got a database query which performs somewhat sluggish in production. The database is a managed AWS MySQL RDS instance.
It is not uncommon for this query to take 5 - 10 seconds.
I know that char(...
- 193
0
votes
1
answer
52
views
PostgreSQL query scanning all partitions instead of specific one when filtering on dimention table
I have two tables in PostgreSQL, one large (transactional) with millions of data which is partitioned on column TenantId and one smaller (dimension) which has TenantId and TenantName columns, I have ...
- 170
0
votes
1
answer
67
views
Nothing happened in my table according to "fn_dblog", is that correct?
Yesterday, something happened at a customer site, and I would like to know if my analysis is correct:
In order to know, what happened, I launched the following query:
SELECT TABLE_NAME, COLUMN_NAME
...
- 377
1
vote
2
answers
37
views
Need help in rewriting query
I have 2 databases test1 and test2. where I need to compare columns from schema1 to schema2 and fetch the results.If table in test1 schema not present on test2 schema ,it should ignore that table. I ...
- 11
2
votes
1
answer
79
views
Recursively sum child's nominal to parent, on dynamic level parent-child structure
i have data look like this :
i try to sum the Nominal column to each respective parent, so it will looks like this :
i provided the sql script for the data :
SET ANSI_NULLS ON
GO
SET ...
0
votes
3
answers
38
views
Select object from first table, who doesn`t have true in second
I have table with objects
uuid
name
98665e5a-e1cb-4bf7-93fa-a8d13983f358
object
and another table with information about enabled this object and another object together
uuid_from_table_upper
...
0
votes
1
answer
23
views
MongoDB index not used depending on query formulation
We see in one of our services that certain queries always result in a COLLSCAN, instead of the appropriate index being used. The data has the form
{_id: DocumentID, "field": {"child&...
- 5
0
votes
2
answers
111
views
script to change the schedule of a job, it needs to find the schedule associated with the job
using Ola's backup strategy,
there is a job
databaseBackup - system databases full, as you can see on the picture below.
this job is scheduled to run once a week, on a sunday morning.
that is all good,...
- 16.6k
1
vote
1
answer
35
views
Make sequential groups of rows that surpass a minimum sum
Let's say I have a table with a timestamp (ts) and a value (a).
With some sample data:
ts, a
1, 10
2, 30
3, 10
4, 20
5, 40
6, 10
7, 20
8, 30
etc
I would like to group rows, in ts order, by summing a ...
- 325
-3
votes
1
answer
47
views
How can I retrieve all hierarchical user data from MySQL tables?
This is a simplified version of my MySql tables.
User table
user_id
user_name
manager_user_id
1
John
Null
2
Maria
1
3
Rob
2
4
Alex
3
User Data table
id
user_id
some_user_data
1
2
abc
2
3
def
...
1
vote
1
answer
98
views
sql server query to show the running jobs - what about the steps of the job?
I have been working on a query to show me the running jobs.
I got it right.
the script is below (enjoy):
DECLARE @JOB_NAME SYSNAME=NULL
SELECT job.name
,job.job_id
,...
- 16.6k
0
votes
1
answer
20
views
Retrieve a delta of records when any related table timestamp passes the date supplied
I am trying to trigger a report from on a primary table when any relation has an updated timestamp greater than 5 minutes ago.
The updated timestamp of the related tables can differ from the primary ...
0
votes
0
answers
57
views
Issue with query performance coming from mysql to mariadb
we were in the process to migrate from Mysql to MariaDB, but we've encountered a performance issue with several of our queries.
The same (already simplified) query
SELECT
count(*)
FROM
vorgang,...
- 9
-1
votes
2
answers
90
views
Sql query result in a single row with multiple dynamic columns
i have multiple rows and want to make dynamic column i tried below code with case it work fine but i need the column to be dynamic based on the count of max Code
EventId
Code
Date
1
A12
12/12/2020
...
- 1
0
votes
0
answers
20
views
Writing a query to return records from one table using records selected from two other tables by their association to a fourth table
I have four tables that are immediately relevant to the question, though there are two others peripherally involved, which I’ll describe for completeness, marked *
Data:
ID
Name
Description
tag
catid
...
1
vote
2
answers
24
views
SQLite Join 2 tables with table1 having two columns that reference table2
I have two tables:
jobs
CREATE TABLE IF NOT EXISTS jobs (
job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
job_name TEXT,
prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id),
...
- 13
0
votes
1
answer
50
views
import extended properties of sql server database from xls file
I have a list of extended properties for all databases in an excel file. How can I import that into extended properties of each database in sql server.
- 1
1
vote
1
answer
246
views
SQL query to get the names of the partitions which are detached from the parent table in postgres?
I used pg_partman to create and drop partitions from my 2 postgres tables. Since there existed a foreign key relations among the tables, I couldn't drop the tables using partman(does not allow).
...
9
votes
1
answer
719
views
Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning
I have the following tables in my database.
LastNames Table
CREATE TABLE [dbo].[LastNames](
[LastNameID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
...
- 18.3k
1
vote
2
answers
244
views
Oracle SQL query slow even with indexes
I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones:
table1:
createtime date
contact_key char(96 byte)
table2:
contact_key char(96 ...
- 163
1
vote
1
answer
98
views
PostgreSQL - Sum all row that satisfy a condition into a single row
I'm trying to extract some statistics from a Postgres database and I made this query:
SELECT
city,
job_count,
TO_CHAR(job_count * 100 / SUM(job_count) OVER (), '90D00%') AS job_share
FROM (
...
- 13
2
votes
2
answers
75
views
How can I find a topmost row and count the rows with only one scan?
Suppose that I have a schema like the following:
-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);
-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
SELECT ...
- 123
1
vote
1
answer
39
views
Balance calculation problem with Foreign Exchange in Mysql
I am trying to make a current balance system for pre-accounting in mysql. I create receipts in different currencies and collect payments in different currencies. My main currency is Turkish Lira (TRY)....
- 11
0
votes
0
answers
35
views
How to mark a calculated query column as total:sum in Microsoft Access?
How can I ensure for Design View that the "total" line of this calculated column shows "sum"?
It seems to change after running the query and saving it.
What starts as:
TotalRevenue:...
- 1
0
votes
2
answers
222
views
Selecting a count of distinct logins per day
I have an activity_log table that I need to derive a basic output of how many distinct users logged in that day, and sum up them up day by day. The table logs all actions of a user and will have ...
- 3
0
votes
0
answers
61
views
How is the ! used in Microsoft Access queries?
The "bang" or exclamation point is a logical operator.
Just to follow up on a previous question, is it problematic to use "!" with Microsoft Access queries to specify tables and ...
- 1
0
votes
0
answers
97
views
How to remove a border or box surrounding a text box from a Microsoft Access report?
After adding a "Country" label and textbox to the underlying query, and then to the report, the "Country" label has a box around it. I cannot find the control to remove this box.
...
- 1
1
vote
1
answer
155
views
Limiting the number of rows that can be inserted for a given WHERE clause
In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?
I have a table event_attendance with ...
- 141
2
votes
3
answers
469
views
Why does SQL Server require a LEFT JOIN to retrieve a status that doesn't exist?
I was recently querying our internal database inventory tool for a list of servers, instances and databases, and was adding the corresponding status to each server, instance and database.
Relationship ...
- 18.3k
2
votes
2
answers
272
views
How to make a Data Modifying CTE abort if an update only partially succeeded?
I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
- 23
0
votes
1
answer
60
views
Problem with saving data SQL
For some time now I have had a problem with saving data in the database.
The problem is that after a sudden shutdown of the server, the data that was sent a minute earlier from the application was not ...
- 1
0
votes
0
answers
25
views
error when running OPENROWSET query - what to do with the FORMATFILE_DATA_SOURCE
I want to run a query from Sql server to Blob Storage all in the same Azure Tenant.
my environment :
SQL SERVER 2022 (running on a azure virtual machine)
Blob storage.
I've ran this scripts (some ...
- 47
-1
votes
2
answers
599
views
Is there a simple way to filter XML datatype in SQL-Server?
I have a table in an SQL-Server database, containing a column, called XmlMsg, being of the datatype Xml:
"Columns" definition:
I would like to filter on that column, which first I did using ...
- 377
0
votes
0
answers
38
views
Interest Accrual and insert monthly records since start date
I need to calculate the (1) "To" date after the initial contribution amount to accrue for accounting interest. The "TO" is calculated as
End of month date if no other are events ...
0
votes
1
answer
107
views
Why is this PostgreSQL query with LIMIT so slow?
I have a .NET WebApi with two entities (please forgive the silly names, it's just an example):
public class Father
{
public int Id { get; set; }
public string Name { get; set; } = null!;
...
- 103
1
vote
1
answer
43
views
Excessive Execution Time
Context:
I have written a t-sql query that works perfectly if I do not use a SELECT with a combining of 2 columns (txtSubID and txtSubmitByInitials). The problem is, I need to combine the 2 columns to ...
- 161
1
vote
2
answers
88
views
Select unique rows based on condition stored in another table
I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below.
# Questions Table:
╔════════════╦═══════════════╦════════╗
║ id ║ ...
- 11
0
votes
0
answers
33
views
SQL: Perform join on multiple tables on Google BigQuery
Please, I am running a professional course in data analytics. Right now, I am on my Capstone project. But I am having challenge combining tables in BigQuery using SQL. The data is about bicycle trips ...
2
votes
3
answers
682
views
How can I get an average from a query dataset in SQL Server?
I am building a parts inventory system, and have a transaction table that stores how often an item in used.
Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
- 23