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
0 answers
15 views

Querying arrays in JSON fields

The DB is Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0. Suppose we have a table CASE_ISSUES with a JSON field ISSUES_JSON: CASE_ID ISSUES_JSON 105 [{&...
Yimin Rong's user avatar
0 votes
1 answer
30 views

Out of my depth with post mysql 8 update (Warning: #1287 Setting user variables within expressions is deprecated)

Now I can get my way around PHP, and I can get around basic MySQL queries, but I'm not really comfy with MySQL, and have inherited an issue that occurred today after our hosting company updated their ...
Adrian M 's user avatar
0 votes
1 answer
39 views

Efficient PostgreSQL query syntax for duplicating the result

Assuming I have the following PostgreSQL query: SELECT ( SELECT SUM (t.customers) FILTER ( WHERE id = 1 AND date BETWEEN %(start)s AND %(stop)s ...
jvkloc's user avatar
  • 125
0 votes
0 answers
5 views

Power Pivot/Data model issue

Good day, I am facing two blocking points where I would need your insights please. I have downloaded all my sheets in power query and made the necessary transformations, including creating a common ...
Maissa Oueslati's user avatar
0 votes
1 answer
26 views

Validate "insert", "update", "delete" in the body of a trigger

I need to create a query where I can remove triggers that have "insert", "update" or "delete" in their body from the result. I'm using the following query for this SELECT ...
Igor Oliveira's user avatar
1 vote
1 answer
16 views

Identify sequences of events starting at well-known start locations, end locations not predetermined, and WITH MYSQL 5.x

I'm working within the limitations of MySQL 5.x. Several security guards are responsible to walk around a property and visit checkpoints. Two specific gates permit entry and each guard must begin a &...
Newbuntudude's user avatar
2 votes
1 answer
51 views

How to locate a procedure in the body of a trigger

I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body -- I'm using ...
Igor Oliveira's user avatar
0 votes
0 answers
24 views

Write SQL query to create output dataset with quarterly exchange rate based on input table with exchange rate by monthly

from_currency month year exchange_rate GBP 1 2023 0.7952 GBP 2 2023 0.7738 GBP 3 2023 0.7934 GBP 1 2024 0....
narrvula's user avatar
-1 votes
1 answer
19 views

select user with least amount of tiquets between all other users

I have a table where I store users info like this user tickets s.lewis 0 a.barrantes 2 x.mendez 3 j.robert 0 I want to create a query to select the user that has the least amount of tickets ...
Sarah Lewis's user avatar
0 votes
1 answer
42 views

Inserting Multiple Rows into a Table and Mapping Newly Created IDs Back to Original rownum in Another Table - SQL Server

I have the following tables -- Create the #product table CREATE TABLE #product ( id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), name VARCHAR(100), createdDate ...
lifeisajourney's user avatar
1 vote
1 answer
41 views

Specific query gives me trouble

I'm supposed to run this query in PGadmin: SELECT     n2.cpr,      n2.adresseringsnavn,     n2."fødselsdato" AS foedselsdato,      n2.morcpr,     n1.mor_navn,     n2.farcpr,     n3.far_navn ...
Maja Thuren Jørgensen's user avatar
-1 votes
1 answer
54 views

Question on joins with multi tables

I have 4 tables ( 1 to many table relation) as shown Main , Worker , Location and MiddleName Table Any suggestions on how to get the result. I tried joins and it never worked.
sunny's user avatar
  • 1
4 votes
4 answers
2k views

What's the order of operations in PostgreSQL?

I'm a DB student, and I executed the following query to learn a couple things at the same time (LEFT/RIGHT JOIN, UNION, WHERE + RegEx). What's troubling me is the order of execution. I have two tables,...
elkronequis's user avatar
0 votes
2 answers
53 views

Replace multiple words in a single value using SQL

I have two tables, #customer and #abbr. The #customer table consists of two columns: 'id' and 'name,' while the #abbr table includes entries for abbreviations and their corresponding full forms. My ...
lifeisajourney's user avatar
0 votes
1 answer
44 views

Performance issues with @> operator in postgres 14

CREATE TABLE public.article ( id uuid NOT NULL, tags _text NULL, CONSTRAINT article_pkey PRIMARY KEY (id) ); I have the requirement to load articles that have a tag which is in a large ...
user1117605's user avatar
0 votes
2 answers
102 views

How to apply JOIN only if there are rows available

Given the following database: CREATE TABLE "Activity" ( "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY, "designation" TEXT NOT NULL ); CREATE ...
Exprove's user avatar
  • 109
0 votes
0 answers
27 views

How to stablish a partitions & indexes stategy in Postgres for 100s of milliion rows

For the purpose of learning, I am playing with Postgress. In particular partitions and indexing. The Tables Let's say I have the following tables on my system: webs web_id --> BigInt web_tld --> ...
Javi M's user avatar
  • 51
0 votes
0 answers
19 views

Keep both non-overlapping intervals and overlapping ones with a larger id MySQL

I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique....
Alex.Kh's user avatar
  • 101
0 votes
1 answer
24 views

MySQL Data not flushing to disk

Lets start with what I did... I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries I copy ...
tek-dragon's user avatar
0 votes
0 answers
24 views

Azure Synapse query to multiple JSON files in data lake, parse data into table format

I want to query RAW telemetry data stored in JSON format in my data lake. I am using Azure Synapse to query the files. I can successfully pull the data I want into a result table, but I struggle to ...
Damo's user avatar
  • 193
-2 votes
1 answer
42 views

I want to assign a character value to a field that contains numbers without using CAST or CONVERT so as to avoid cardinality estimate warnings

I'm looking at something like SELECT CASE WHEN MyField LIKE [0-9] THEN 'xyz' WHEN MyField LIKE [10-99] THEN 'abc' WHEN MyField LIKE [100-999] THEN 'def' WHEN MyField LIKE [1000-9999] THEN 'ghi' ELSE [...
Eyespi20's user avatar
0 votes
0 answers
23 views

SQL Select based on pattern

I have a SQL column in my system that has a bunch of file names. I am attempting to separate the file names based upon a matching file name pattern. Some files have a middle initial and some dont. ...
user1879840's user avatar
0 votes
0 answers
46 views

Does Athena have ref cursors?

Does Athena have a Ref Cursor type construct ? Our team members have written pyspark sql in the following format SELECT dr.case__c as caseId, collect_list(struct( dr.id as documentId, ...
adbdkb's user avatar
  • 103
1 vote
0 answers
53 views

Adjust SQL query to select only one record

I have three tables: An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null). id alignent_id ...
ioni's user avatar
  • 61
0 votes
1 answer
26 views

Strange query behavior in PostgreSQL

Database is PostgreSQL15. I am writing a query that will return a list of table partitions I need to trim. My partitions are named as such: table_YYYYDOY, where YYYY is the year and DOY is the Day of ...
EᑎOT's user avatar
  • 103
0 votes
0 answers
32 views

Query takes really long

I am running into issues with this query. I am running on a VM, windows server 2019, MSSQL Server 2022, 128 GB of ram, and 8x of these processors: Processor Intel(R) Xeon(R) CPU E5-2697 v4 @ 2.30GHz,...
MichaelB's user avatar
0 votes
0 answers
31 views

How can I duplicate adjacency lists without breaking parent-child relationships?

I would like to duplicate hierarchical structures, modelled as adjacency lists, in my database. The following tables and records allow a minimal reproduction: CREATE TABLE item_groups ( id serial ...
Check12's user avatar
  • 53
0 votes
1 answer
31 views

Oracle MAX gives different result than ORDER BY desc

When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting: with some_data as ( select '123A55' as hex from dual union all ...
Andy DB Analyst's user avatar
0 votes
1 answer
38 views

Select distinct

I had some confusion regarding distinct keyword... For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
datascinalyst's user avatar
-2 votes
1 answer
52 views

Oracle SQL - Selecting First Row for each ID

I am using Oracle SQL Developer and I am trying to pick the first entry of the student population. So, that I have one row for each ID. ID DEGREE AWARDED ACADEMIC PERIOD 147 GR MS 2022 147 UG BS ...
DataAnalyst's user avatar
0 votes
1 answer
41 views

New column that subtacts 2 rows from a different column and pushing out the difference number

I am doing this query, I want to be able to take 2 values from different rows from 1 column(Population growth rate) subtract them from each other, and put that value into the new column(...
Lqvy's user avatar
  • 1
1 vote
4 answers
56 views

Loop through 30 columns in Oracle DB to check if a value exists

I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it. ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE ...
Rellek's user avatar
  • 13
0 votes
1 answer
55 views

Order by problem caused by the '_' character

I'm using MSSQL Server 2019 and I've some data like; | FileName | |----------------| | DummyData._0 | | DummyData._1 | | DummyData._10 | | DummyData._100 | | DummyData._101 | | DummyData....
mbielsa's user avatar
  • 42
0 votes
0 answers
19 views

Generate list of tables/ views hit by a list of queries in Snowflake

Snowflake provides a list of queries against the databases in account_usage.query_history. I'd like to dive deeper into what tables/ views are being hit most often -- but often there are very ...
user45867's user avatar
  • 1,651
0 votes
1 answer
72 views

How to speed up query for stock balance?

I have 2 tables StockIn and StockOut and calculated stock balance (in-out). Both tables contain millions of records. When I calculate the stock balance, due to the millions of records, my query ...
Umar's user avatar
  • 3
1 vote
1 answer
120 views

How to update multiple records after insert/update in SQL Trigger?

I have below query, which updates timestamp for 10 records. UPDATE [dbo].[file] SET timestamp = GETDATE() WHERE id <= 10 I created a trigger on that table Insert/Update. SET ANSI_NULLS ON GO SET ...
Mike's user avatar
  • 13
1 vote
0 answers
27 views

Possible explanation for why "SELECT" query executed from JDBC returning just the first row in the table

I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code: static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE"; static final String ...
Bogdan Floareș's user avatar
0 votes
2 answers
75 views

Comparing 2 count columns with case

I have 2 tables, size and cost. My query does a count with a greater than comparison of cost and counts different sizes. table1: size u table2: cred t Here is my query: select u.size as size ,...
tkmagnet's user avatar
0 votes
1 answer
38 views

Make a query where the colums are based on finding text from another table

This is probably easier to explain with a toy example. I have a MSSQL database with two tables. Firstly, I have table A, which has two columns: "Dish_name" and "Dish_ingredients". ...
Variax's user avatar
  • 101
-1 votes
2 answers
362 views

PostgreSQL: Issue Declaring Variables in Query

I'm trying to declare a variable for later use in a PostgreSQL query. I've done this often in TSQL, but I wasn't sure about the syntax. The stuff I've seen online all points to something like this: ...
Lexen's user avatar
  • 3
2 votes
3 answers
56 views

Find percentage based on values for each ID

I have table1 with 2 ID's and 2 values per ID (Y,N). I can count the values by the following query: select id ,count(*) as "total" ,choice from table1 where id in (1,8) group by id, choice ...
tkmagnet's user avatar
0 votes
1 answer
45 views

Join 1 row out of multiple 1:1 tables and return as array of json objects

I have a database structure like so (in Postgres): An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types ...
Gmanicus's user avatar
0 votes
0 answers
36 views

Faster running for this query sql server

i used database engine tunning advisor for creating some index and statistics but still run slow SELECT C.code_commande AS 'Order', C.code_client AS 'Cust&#46', ...
octavian darie's user avatar
0 votes
1 answer
33 views

How to find missing number from different ranges

I have a table with two columns, one containing name and other numbers. For each name there is range of numbers and there are around 250 names in there. Range of every name is different. It may be 1 ...
Indresh Mangal's user avatar
1 vote
1 answer
55 views

creating several records from one, based on a numerical value stored in a field of the source record

In a PostgreSQL db, I have a table with a numerical field, among others In such field, some natural numbers are stored and they represent the number of occurrences of something like this, roughly ...
user1632812's user avatar
0 votes
0 answers
12 views

Find document with regular expressions in collections related to populate in mongoose

The aim is to filter a collection of documents (Requests) based on the document field from another collection (Patients, Clients, Doctors, and Services), of which there are several references in the ...
Izlia's user avatar
  • 101
0 votes
0 answers
21 views

Rows matched but changed 0

I have been scratching my head for hours. Query matches but nothing is updated. Mariadb 10.6. UPDATE field_data_field_coupon_description SET field_coupon_description_value = 'Limited time only ...
JM John's user avatar
  • 13
0 votes
1 answer
187 views

Finding when columns are added- SQL Server

In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
Farid Zahidov's user avatar
0 votes
0 answers
9 views

Implementing First In / First Out for fuel transactions (such as purchases at varying prices and transfers between locations)

After being tasked with trying to improve the way my company keeps the fuel inventory, i hit a wall. My SQL knowledge is pretty basic and i don't even know where to start. A little background: The ...
jonatan hellborg's user avatar
0 votes
0 answers
22 views

Finding tree contours using nested set model

Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model? I have described tree ...
Chris Walton's user avatar

1
2 3 4 5
31