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
-1
votes
0answers
23 views

PostgreSQL optimize LEFT JOIN and GROUP BY query

I have been dealing with a large SQL query with multiple LEFT JOINS and a GROUP BY statement. The bottleneck of the query seems to be the GROUP BY part. Here is the query : SELECT SUM(CAST(l.value AS ...
2
votes
1answer
50 views

Query Store takes a very long time to load duration

I'm running Query Store to make a DB perform better. It's been working fine until now.. When i try to load the Top durations of last day it took me 26 min to load the screen. The SQL version im ...
0
votes
1answer
10 views

How can I query my "subtype" tables?

I am trying to model inheritance in my database. The purpose of the database is to use it to emulate a directory structure in a desktop GUI. The directory structure is made up of nodes, which can ...
-2
votes
1answer
27 views

Write 2 complex queries

I have a DB: CREATE TABLE cabinet ( id_cabinet INT PRIMARY KEY, area INT NOT NULL ); CREATE TABLE doctor ( id_pib INT PRIMARY KEY, speciality VARCHAR(50) NOT NULL, surname ...
0
votes
0answers
34 views

SQL query to get daily record for an event

I posted before and made some progress so posting again but with a different question: I now have this table with the following data: start_time end_time id ...
1
vote
1answer
32 views

Can I retry every SQL query in case of a deadlock exception (MSSQL)

Is it always safe to retry an SQL query in case of a deadlock exception? Can this lead to unexpected behavior or executing an operation twice? I think a deadlock exception always means a rollback even ...
0
votes
1answer
37 views

SQL query that is able to count days based on event queries

I have a table with the following data: id date_changed color_start color_end ------------------------------------------------------------------- 1 2020-05-27 16:33:52....
1
vote
1answer
35 views

Allocation of resources query

I have two tables, one for resources and another for the required allocation. For instance: Resource Count A 3 B 2 C 8 Required Count X 4 Y 3 Z 5 The resources table tells how many things I'...
1
vote
1answer
21 views

Slow simple select query on Postgresql 13 RDS hosted indexed table

I am working with a simple table, hosted on RDS instance running Postgresql 13. It is using t3.small instance with general purpose SSD storage. The table and its index look like this measurements=> ...
-1
votes
1answer
28 views

Command basics, look through three tables

Hi, I'm starting to learn mysql and am confronted with the commands. I think I understand the basics, but with this example I'm failing to set up the commands correctly. The underlined keys are ...
1
vote
4answers
45 views

Postgres cast split string to double precision

I have a cost column in my postgres db whose type is varchar. It stores the costs of each service a client gets in this format "KES 0.80". I am trying to sum up everything to get a cost ...
0
votes
1answer
19 views

How to join an exsiting table to self-join query table?

This is the self-join query: select g.discription, m.title from genre g join genre m on g.title = m.main_title This is the table that I want to join: select band.name, band.genretitel from ...
0
votes
1answer
25 views

Require assistance to write Query joining 3 tables

I have a table called form_submission_entries which contains data like this: id submission_id field_id value 1 1 1 John 2 1 2 Doe 3 1 3 27 4 2 1 Jane 5 2 2 Doe 6 2 3 23 The field_id ...
0
votes
1answer
17 views

How to get available worker from a roster database?

I am using Postgres, and I am working on a database for a roster system like this: drop table if exists sessions; create table if not exists sessions( id int GENERATED ALWAYS AS IDENTITY, name ...
-3
votes
1answer
24 views

Sql 2008 export table csv with headers and data

I need to export a table in SQL Server 2008 with headers and data delimited with | ( pipes ), but some information inside of this table have , ( comma ) and CR or LR, it's possible to put it a "&...
0
votes
1answer
20 views

Query to show whether a record is appeared in the previous date or not

I have a table with below structure and sample data: create table TEST_ONE ( purchase_date DATE, customer_id NUMBER ) | purchase_date | customer_id ---------------------------------- |...
-3
votes
1answer
35 views

SQL QUESTION_Case When + LIKE % [closed]

I get an error but I don´t understand why. Does this make sense in SQL? I need the column ValidNaming to be filled with False/True if the customer (or field CommonLastName) has an invalid name. Why ...
-1
votes
2answers
47 views

How to conditionally pick a row based on status value?

I want to select all available and bought packs. An user can open two pages to buy one pack, this results in two rows in the payment table. One gets status 'Pending' and the one that is bought gets ...
-2
votes
0answers
16 views

How can i add a new column that shows the names of columns that have been updated?

I have a table with all the changes for employee records, currently I need to display what columns have change when compared to the other rows. so far I am able to display all the distinct rows that ...
-3
votes
0answers
24 views

ORA 20014 ERROR USER DEFINEEXCEPTION WHEN TEST ING THIS I GOT ERROR FILE NOT EXIT I AM PASSING FILE FROM PROG ERROR AT LINE 3 LINE 43 AND LINE 111

create or replace procedure SP_webconfigsave( Oresult out Varchar2, Icsoftwaretype char, ...
0
votes
3answers
37 views

Does using "qb_name()" hint (query block name) in a query have significant effect on performance?

I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query: select /*+ qb_name(main)...
0
votes
0answers
6 views

Why does Access recalculate a stored query when I copy it? Can I tell it not to?

When I copy and paste a stored query in Access, it's usually because (a) I want to back up the code before making changes, or (b) I want to make a new query by making changes to that one. All I want ...
0
votes
1answer
49 views

SQL Insert by subquery inside update query [closed]

I want to Write a single query that updates columns in one table and insert rows in another table. something like this update applications set applications....
6
votes
3answers
237 views

How can I calculate all grouping permutations of an input string in SQL?

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD&...
0
votes
1answer
33 views

Select Distinct with Inner Join Bringing duplicated Values

I am having trouble using inner join to bring unique values from two tables. SELECT distinct cast (opd.order_number as integer), p.payment_id , pt.transaction_status, opd.installments_number, ...
0
votes
1answer
25 views

How to set the column names dynamically in crosstab?

I have a table with key and value and I have saved the values as shown below when I use the below query select * from crosstab ( 'select rowname,colname,max(cellval) from table_to_pivot group by ...
-1
votes
1answer
39 views

SQL Query for multiple rows

Good day. I'm trying and failing to write a mySql query that will return custid only if that custid has multiple values. In the example data below I want to return custid only for customers with type ...
0
votes
0answers
50 views

UPDATE column with previous value

Imagine a sample table CREATE TABLE mytable (myid integer, myval double precision); I want to update myval to the previous value, ordered by myid, if a condition is met (that myval is equal to 0). ...
2
votes
2answers
84 views

Recursive query on a self-referential table where each node has one link to its child's node

Consider the following table and data (fiddle available here): CREATE TABLE test ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, next_id INTEGER NOT NULL ); INSERT INTO test (id, next_id) ...
0
votes
2answers
110 views

MySQL multiple subqueries - slow ordering

Any idea how to optimise this query? It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds. Problem is, that I need to been ...
0
votes
3answers
62 views

I want to find out what data has changed across a row from table 1 to tabl 2 [duplicate]

I have two identically set up tables that track water samples, the first table was in June and the second was taken in November. I need to produce a report on what values have changed over time. I ...
2
votes
0answers
75 views

T-SQL : Slicing time periods by related time periods

Consider the following table that gives the validity dates for which an Item is associated to something called a Demand_Unit, and a certain Market : Item_ID Start_Date End_Date Demand_Unit Market X ...
8
votes
3answers
2k views

Improper use of logical operators has led to poor query performance

I have a table with huge amount of data (Nearly 15 million) and below structure. create table test (a int,--> /* There is a normal index on this column */ b int, <other columns>) There is a ...
-2
votes
2answers
50 views

joining table 1 with table 2 where there are multiple matches in table2 and you want 1 but definitely not the other

Here's a simplification of the problem... suppose you have a bunch of socks in a drawer. Each sock has an id associated with it, but they might be different striped colors. Let's say the stripes are ...
0
votes
2answers
53 views

Optimal index for a nested query?

I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance. The table contains > 2 Mio rows counting. I am working with Oracle. ...
1
vote
1answer
107 views

PostgreSQL query for parent - child relations

I need to store objects in a PostgreSQL database version 13. Each object has an ID, a name, occupies a level and belongs to a parent object, relation denoted by belongs_to. The values for the levels ...
0
votes
0answers
13 views

Query for getting all data in a particular timeframe for the respective agent_id

Currently I'm using CodeIgniter and MySQL to retrieve a count of data within a particular timeframe. My table data looks something like this: Where there are different agents who are changing ...
0
votes
1answer
53 views

Counting number of records newer than X date matching a condition quickly

I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions. Here is basically what I'm working with: CREATE TABLE ...
0
votes
0answers
19 views

SQL Query Statement with Group By

I am trying to analyze how far players of the game progress as a function of age. The age is measured as the number of days since game installation. For active players who played the game at a certain ...
1
vote
2answers
68 views

Select * - Why or Why Not?

Is there really any difference between these two queries? SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table] SELECT TOP 1000 * FROM [database].[schema].[table] ...
0
votes
1answer
21 views

Column collision in Left join with many columns vs PHPMyAdmin resultset

I have 4 tables (75 columns each table approx) but they share columns with the same name: Table Master_Producto Master_Product.PD_Prof_21 Table Product_Structure Product_Structure.PD_Prof_21 I am ...
-2
votes
1answer
43 views

query to get start time and end time from same column based on boolean value order by primary key [closed]

As of now, this is my current query: SELECT MAX(CASE WHEN status = 1 THEN [timestamp] END) as machineOn ,MAX(CASE WHEN status = 0 THEN [timestamp] END) as machineOff FROM machine_table where ...
0
votes
1answer
80 views

Unnest array values from multiple columns with dynamic unpivot

I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns. Example input: CREATE TABLE tbl_wide ( p_key integer PRIMARY KEY,...
-1
votes
1answer
44 views

How to pass a parameter into a subquery in a join statement [closed]

I have a query that should be implemented in an MSSQL View so no local variables allowed, this query is working but it has to call a function to return a temporary table, this function is called ...
0
votes
0answers
18 views

Sql query + wpml in one language only

I'm not a developer, just a normal user, so please don't expect me to be like you :) I'm managing my website in WordPress, Running 4 languages using WPML, (en,de,cz,pl) My concern is, I want to search ...
0
votes
1answer
50 views

how to get result value JSON and send result Format JSON

I have Query for result JSON but I need result like this: result:[ "B":{1628424359816,35}, "A":{1628424359816,1}, "B":{1628424380816,63}, "...
0
votes
2answers
38 views

LIMIT the number of rows in GROUP BY and GROUP_CONCAT with ORDER

In this sqlfiddle, CREATE TABLE t1 ( id int(11) unsigned NOT NULL, val int(11) unsigned NOT NULL, rank mediumint(7) unsigned, INDEX(id), INDEX(rank), PRIMARY KEY(id,val) ) ENGINE=InnoDB DEFAULT ...
-2
votes
1answer
45 views

Find a doubled value between two columns in the same row

We discovered a bug in our program where there are some instances of a doubled value. I am trying to write a query to find all instances of this doubled value. Right now I am using WHERE (GRIR....
0
votes
1answer
19 views

select time intervals from a nested query

I have a table which has the following columns: id, start_timestamp, device_id I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps. For ...
-1
votes
1answer
48 views

mysql vs mariadb query [closed]

Hope i don't make any mistakes posting here :) Hi everyone, i'm using the following query on mysql and works perfect. But when i use the same query (same database) on a mariadb i'm getting wrong ...

1
2 3 4 5
25