Skip to main content
The 2024 Developer Survey results are live! See the results

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).

Filter by
Sorted by
Tagged with
0 votes
2 answers
29 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 ...
Sultan's user avatar
  • 1
0 votes
0 answers
11 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 ...
FitzmorrisPR's user avatar
-2 votes
0 answers
20 views

Dynamically change column names based on an 8 digit binary field

I have these two tables The value in the incentive programs table is the decimal values based on binary values 00000001 (1), 00000010 (2), 00000100 (4), 00001000 (8), 00010000 (16), 00100000 (32), ...
jhogan3's user avatar
  • 27
1 vote
2 answers
20 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), ...
Erich4792's user avatar
-1 votes
0 answers
36 views

How to get limit of 3 months date record in sql server?

I have created a view on the table and want to use that view so that users don't need to manually change it from the excel sheet, under data source connection. In excel sheet what query can accomplish ...
thrue2023's user avatar
0 votes
1 answer
42 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.
Ramon's user avatar
  • 1
1 vote
1 answer
37 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). ...
Kushagra Kumar's user avatar
9 votes
1 answer
693 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 ...
John K. N.'s user avatar
1 vote
2 answers
82 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 ...
Gábor Major's user avatar
1 vote
1 answer
36 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 ( ...
Mateja Maric's user avatar
2 votes
2 answers
60 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 ...
user570286's user avatar
1 vote
1 answer
35 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)....
Ali Turhan's user avatar
0 votes
3 answers
47 views

Split data by days

Is there a way in PieCloudDB Database to divide a piece of data into multiple pieces of data based on the start time and end time in days. For example: id value start_date end_date 1 ABC 2024-01-29 ...
Joe's user avatar
  • 5
0 votes
0 answers
18 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:...
Nick's user avatar
  • 1
0 votes
1 answer
99 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 ...
JDBA's user avatar
  • 3
0 votes
0 answers
33 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 ...
Nick's user avatar
  • 1
0 votes
0 answers
37 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. ...
Nick's user avatar
  • 1
1 vote
1 answer
69 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 ...
Luke Hutchison's user avatar
2 votes
3 answers
467 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 ...
John K. N.'s user avatar
2 votes
2 answers
246 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 ...
cynodon's user avatar
  • 23
0 votes
1 answer
52 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 ...
DanPil's user avatar
  • 1
0 votes
0 answers
15 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 ...
dexon's user avatar
  • 47
-1 votes
2 answers
297 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 ...
Dominique's user avatar
  • 581
0 votes
0 answers
27 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 ...
Myran thiru's user avatar
0 votes
1 answer
87 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!; ...
Alessandro's user avatar
1 vote
1 answer
38 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 ...
MIS_Gem's user avatar
  • 161
1 vote
2 answers
60 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 ║ ...
Rohit's user avatar
  • 11
0 votes
0 answers
19 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 ...
Nduka Charles Jala-Igwe's user avatar
2 votes
3 answers
639 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 ...
Viper75's user avatar
  • 23
-1 votes
3 answers
81 views

Sql Query to get the active products during a certain timeframe

I have the following table Create table #products ( productId int, regionId int, date datetime, active bit ) insert into #products values (1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',...
lifeisajourney's user avatar
0 votes
1 answer
98 views

PostgreSQL: What's the most accurate way to find the network bandwidth of a Query result set

I have been trying to figure out network bandwidth of a Query output, since PostgreSQL only holds I/O bandwidth of Query I ended up in Octect_length() or pg_column_size(). I found below query from ...
goodfella's user avatar
  • 168
1 vote
2 answers
52 views

Query to return latest record between 2 tables

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data: create table customer ( cusID char(5), cusPurDt ...
tkmagnet's user avatar
-1 votes
1 answer
51 views

How do I partition my database by rowid and query the partitions properly?

Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called ...
KillerDiek's user avatar
2 votes
2 answers
247 views

Snowflake won't allow an analytic function to be renamed

In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails....
James Madison's user avatar
2 votes
1 answer
56 views

Query for single return meeting multiple criteria and values

I'm trying to figure out a way to count an ID once when multiple criteria are met in one column while displaying a total of the same criteria in another column. The criteria for the first column can ...
tkmagnet's user avatar
0 votes
2 answers
48 views

Select random records until a cumulative total is exceeded

I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X. select ...
Ashley's user avatar
  • 103
1 vote
1 answer
19 views

In MS Studio how to SELECT from an existing SQL file

I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this ...
TonyG614's user avatar
0 votes
1 answer
86 views

Delete million of rows from parent and child tables in SQL Server

I have three tables: Records, ChildTable, and ParentTable. ChildTable and ParentTable both have RecordId as the foreign key. My aim is to delete all records older than 300 days from the Records table, ...
lifeisajourney's user avatar
0 votes
1 answer
127 views

How to update a Postgres row atomically, with a computed value that depends on the new values

I have the following table in Postgres: CREATE TABLE avg ( user_id INT, numer DOUBLE PRECISION, denom DOUBLE PRECISION, frac DOUBLE PRECISION ); I want to accomplish the following, ...
Luke Hutchison's user avatar
1 vote
1 answer
39 views

How to "move" data from rows to columns with aggregate data?

I count the number of occurrences by the event type, so this is the SQL query: select date(created), event_type, count(*) from public.event group by date(created), event_name Is it ...
mimic's user avatar
  • 111
1 vote
1 answer
90 views

Query to find when a specific current value changed

I have 3 tables of data (all contain more columns, but these are the ones I need from each): t1: table1 workID1 h1Amt h1Dt status 101 73.00 12/1/2023 A 124 0.00 10/30/2023 D 251 0.00 3/1/2023 A ...
tkmagnet's user avatar
1 vote
1 answer
72 views

Add values and join rows on condition in a Mysql query

I have a table with time-in/time-out data in a structure as shown below. id date timein timeout duration 3 2024-01-02 01:00 02:00 01:00 3 2024-01-02 02:30 07:00 04:30 3 2024-01-02 09:00 09:10 00:...
Shahab Mohd's user avatar
0 votes
0 answers
31 views

Having an issue with duplicates after I insert into my suppression table

Hey guys thank you so much in advance for the help. I do mailings every 3 months. I know I am missing records where there is a null. I delete those so that's not the issue I noticed one of my queries ...
ocean waves's user avatar
0 votes
1 answer
228 views

Postgres filter timestamptz column by date very slow (possibly index not used?)

I have the following table with 462541359 rows. create table "Prices" ( "Id" bigint generated by default as identity constraint "PK_Prices" ...
derteufelqwe's user avatar
1 vote
2 answers
100 views

How can we analyze everything an application does in SQL Server? [closed]

I'm not a DBA (yet) but this seems a DBA-type question rather than a programming question. I've been asked: Which queries are called most frequently? How many times are they called during a normal ...
smartwombat's user avatar
1 vote
1 answer
145 views

SQL query classifying types of nodes in a binary tree

I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree. Specifically, the question states as follows: You are given a table, BST, containing ...
Squirrel-Power's user avatar
0 votes
1 answer
29 views

MySQL Query duplicates sum for left join

I have 2 tables, Greeter_Detail and ZipCode_Details I'm trying to SUM the total of People and Families from and Greeter_Detail and join them on ZipCodes, but I get duplicates. ZipCode_Details has city ...
angel12333's user avatar
2 votes
1 answer
93 views

Joining tables using the longest matching ltree path

Given a table like so: path (ltree) a.b.c a.b a d.e f How would I write a query to return the longest matching ltree path given an input? For example: (input) => expected output (a.b.c) =>...
cjheppell's user avatar
  • 213
1 vote
2 answers
488 views

How to search text in oracle view

I need to create a filter where I can locate specific text in my views. I tried using the following queries: SELECT OWNER, VIEW_NAME FROM DBA_VIEWS WHERE UPPER(TEXT) LIKE UPPER('%ABC%'); But ...
Igor Oliveira's user avatar
0 votes
0 answers
29 views

How to do many to many query that returns dynamic number of columns?

I have 2 tables that have a many to many relationship. An items table and a properties table. They are connected by a junction table. Basic versions of the tables below. item_name sword helmet axe ...
Brian Hung's user avatar

1
2 3 4 5
32