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
vote
0answers
62 views

How to get all parents associated to a child, one row for each association [duplicate]

I have a table with two columns, Parent and Child. i need to get an output list showing a child and all associated parents, one parent per row. The youngest child will show as a child in rows equal ...
0
votes
1answer
47 views

Split time in intervals of 15

This is my table with data: I want to select it as: Where 0=office, 1=home,2 = telemedice The time will always be a multiple of 5. How can i do that? I have tried this: WITH CTE AS (SELECT ...
13
votes
1answer
144 views

Сardinality estimation of partially covering range predicates

At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step. On the Internet, at cardinality-estimation-for-and-for-...
3
votes
1answer
169 views

Incorrect syntax near '+' in stored procedure sql server

I am writing a stored procedure which will accept a parameter and send mail to some recipients. I am using the sql builtin stored procedure. here is the code create proc send_mail_admin(@usage_data ...
0
votes
1answer
23 views

Sub Query with group by returns more than 1 row Issue

I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result. This is my query SELECT Count(*) ...
0
votes
0answers
15 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
1
vote
1answer
25 views

How to INSERT all entries to a map table

I have a map table as CREATE TABLE keymap ( col1 int(11) unsigned NOT NULL, col2 int(11) unsigned NOT NULL, col3 int(11) unsigned NOT NULL, FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE,...
0
votes
0answers
35 views

Variable Records

This question keeps coming up at work & collectively we can't think of an easier way to do it than a SQL pivot & huge case statement. So I'm hoping someone might have an idea that could help. ...
0
votes
1answer
42 views

How to select distinct? not select duplicated row

I have 2 table select DISTINCT (B.MAIN_ID), A.NAME, A.NAME_ID, A.CREATED_DATE from FAMILY A, ADDRESS B where B.F_ID = A.N_ID result: MAIN_ID | Name | NAME_ID | CREATED_DATE 100 | ...
0
votes
0answers
43 views

Why is my continuous query notification script only returning events from production database?

What I am using: Python 3.7 Oracle 12c (development) Oracle 10g (production) RedHat 6 cx_Oracle 7 oracle_client 11.2.0 What I am doing: Building a python listener that uses continuous query ...
0
votes
1answer
62 views

Semantic of SQL Query for summing up values

I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column? select * sum(value) over (partition by prename order by NumberOfDays) SumValue ...
-1
votes
1answer
41 views

Select column if specific type of child record exists otherwise fallback

I got a table named "Order", an order has multiple child records in the table "OrderStatus". The orderstatus contains steps following the progress of an order. The orderstatus contains a datetime ...
-2
votes
0answers
12 views

convert row to column or pivot table in mysql [duplicate]

i have the following table but i want to display this pivot table manner, heading is : emp_id (non repeating), all the calendar_date row emp_id (non repeating), attendance_status
0
votes
1answer
75 views

Join one-to-many field in a single result without concatenation

I suffer from the same problem as in Concatenate one-to-many field in single result?, but I need a result where the 'many' fields occupy their own columns and aren't concatenated in one column. Any ...
0
votes
1answer
23 views

Set the file name in “SELECT … INTO DUMPFILE”

I am trying to write a query to dump a single LONGBLOB data into a file. SELECT data FROM table WHERE id = 123 INTO DUMPFILE fileName which data, fileName, and id are the fields in the table. SQL ...
0
votes
1answer
33 views

Query to select specific columns based on current date

I have a table named 'mrtg' that has four columns: mysql> select * from mrtg; +----------+------+------+------+ | city | day1 | day2 | day3 | +----------+------+------+------+ | Delhi | ...
0
votes
1answer
18 views

How can I set a random timestamp for a row based on a columns value in mysql?

I have an app that I built and found an issue with it. Essentially there are created_at and ended_at timestamp columns. The ended_at function in my app had a bug so now I have a few hundred ...
0
votes
2answers
29 views

Database Offset & Limit without order by?

To my understanding, in MySQL/Postgres database there is no guarantee for the order of values with a select statement without an order-by clause, thus one should be getting rows arbitrarily. If so, ...
0
votes
0answers
6 views

How to get records from one collection based on role hierarchy assigned from another collection in mongodb using $graphLookup aggregation

I have a roles collection whose schema is like this : { name:String, parent:{type:Schema.Types.ObjectId, ref:"role"} } it has 3 documents right now as shown below { "_id": "...
0
votes
1answer
37 views

Is It Okay to Leave the Connection Open and Set CommandTimeout for Long Durations?

I have to process daily files and it's a long process that deletes, imports and updates processes. Like literally 20 to 30 minutes per file for several files a day. ** EDIT: I also have some ...
0
votes
1answer
30 views

write SQL query that takes its own output as input

In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade: 713613 PostPTETradeIDs 718478 713613 ...
1
vote
0answers
46 views

Query disk performance in VMware database

i need help to build a query to retrieve disks performance from VMware database. The query must bring the host LUN or the VM disk, and their respective IO and latency. I found the view ...
1
vote
1answer
43 views

Oracle 11g alternative options for oracle 12c with function

I am using (with function) in Oracle 12c. in 11g this is not available. Is there any other way we can implement with function in 11g? with function x(p_NUM in number) return number is n ...
-1
votes
1answer
63 views

How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?

My situation (greatly simplified) is as follows: Table1 ID Name Ver1 Ver2 Ver3 Ver4 Table2 ID Name ... This table holds the versions The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
1answer
27 views

How to troubleshoot hung queries in PostgreSQL RDS?

I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few ...
1
vote
2answers
50 views

Joining unrelated data

I have two tables. Each table has a single column with 2 records. How would I go about merging the columns from both tables into another? For instance, the first table has records 1 and 2, and the ...
0
votes
1answer
19 views

Postresql dynamic index creation for many tables

How can I create an index in Postgres dynamically over many tables but always with different names. Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
0
votes
2answers
43 views

How to count decimals without zeros

How can I count all decimal place up to the first zero For example I have a column named x with an entry of numeric datatype value 1.955000000 I want to count the decimal places without the zeros. ...
0
votes
0answers
33 views

How to check if a sequence exist in a table

Let's assume I have a table made up of 6 columns, each contains a number. Hence a row of 6 numbers makes a sequence. Now let's say I have an array made up of 6 numbers. How can I check if some of ...
0
votes
0answers
18 views

How to refresh the URI type data : insert into temptable values (SYS.URIFACTORY.getURI('URL'));

How to refresh the URI type data and reload to table , whenever the website url got refresh. My complete code: Created table with URI TYPE create table vignesh3(url URIType) loaded data ...
1
vote
4answers
133 views

remove leading and trailing quotes

I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them. I have ...
2
votes
3answers
58 views

Returning Result Based on 2 conditions from one Table

Problem Statement: I have a table that holds three pieces of information (Number, Id, Type). Let’s call it table A: ╔══════╦════╦══════╗ ║Number║ Id ║Type ║ ╠══════╬════╬══════╣ ║ 1234 ║1111║Test 1║ ...
2
votes
1answer
22 views

Gather values from different related rows with JSONB fields

Say I have the following table structure: jobs +----+------------+ | id | some_field | +----+------------+ | 1 | some_val | | 2 | some_val | | 3 | some_val | +----+------------+ events +----...
0
votes
1answer
18 views

mysql - query which provides additional column with iterative number by condition

I do have a table which looks like this. Already grouped by user_id AND activity date: [user_id] [activity_date] 123455 '2019-07-01' 123455 '2019-07-02' 123455 '2019-07-03' 222222 '...
1
vote
1answer
34 views

T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

I have a table with below structure : FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int) based on this table I wrote this query SELECT ...
0
votes
2answers
29 views

Update with rolling sum grouped by another column

I'm trying to build a query to update a MySQL 8.0 table that looks like this ----------------------- user_id | total | delta ----------------------- 1 | 0 | 30 1 | 0 | -10 ...
0
votes
1answer
26 views

Using WHERE / AND in Joins

Can someone elaborate on how the following two queries would provide different results? That is, how the query is processed differently using WHERE or AND. select orders.*, accounts.* from ...
0
votes
1answer
38 views

Query to get the physical file name of the database

Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
1
vote
0answers
21 views

Selecting Duplicates on All Fields

I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into. Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some ...
0
votes
1answer
52 views

How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time? The query ...
0
votes
4answers
91 views

How to check 'contains' in sql?

Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM' How do I load all rows, with 'allotedto' column containing any one of given input values? (eg: all rows alloted to RAM or KLM) ? ...
1
vote
2answers
53 views

Show Primary and Secondary Accounts in a table

I have a table that provides the relationships between Primary and Secondary accounts which looks like this: CREATE TABLE testRelate ( AccountRef nvarchar(20) PRIMARY KEY, RelationshipType ...
0
votes
2answers
22 views

How to get count of each column data

I am trying the fetch the data of the each user of each month. The table looks like below ---------------------------------------------------------------------------------- | user | pm_jan ...
0
votes
2answers
27 views

Create a row based on a condition (column value)

I have a table that contains the following data; EmpNo | GivenName | PreferredName --------------------------------- 1 | Paris | Paris 2 | Ashley | Ash I am trying to get an output ...
0
votes
0answers
20 views

Date between intervals dynamic

I have a table with all employee licenses and two dates in relation to a period of 2 years. I have to verify if have someone period sum of 30 days of license in one range 12 months For example: data ...
0
votes
1answer
84 views

Sql, get category tree for values from one table, names and ids

What I need I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category). Example On web there is product with category tree. In table "category" I have ...
0
votes
2answers
106 views

CHARINDEX equivalent in DB2

I am trying to select the domain from the column where I have email addresses. For example, I have a table email_table and a column email with email addresses as below. johndoe@gmail.com jhefid@...
0
votes
2answers
22 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
0
votes
2answers
38 views

How to select top 3 vendors without duplicate entries?

Here is my tables I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1, V4, V3 without using while loop. is this possible? Table details CREATE ...
0
votes
1answer
34 views

Select query with custom column and value of other column

I want to have a query that have a custom column and have some value of other column here is example of a table table 1 : id, name, emp_id I want to have input like this id | name | emp_id | ...