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
2answers
79 views

How to add a day/night indicator to a timestamp column?

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
0
votes
0answers
3 views

Mongodb tree structure questions and answers

I'm looking to build a question answer survey system where some questions will be based on the answer of the parent question. The hierarchy level of the questions can go any number of depth based on ...
0
votes
0answers
15 views

Why Mysql only updating single row when the where condition have multiple rows? [closed]

My this query always update only single row, while there are multiple rows with gender 'M': UPDATE users SET hire_date = DATE((SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2014-1-1') + FLOOR(RAND() * (...
0
votes
1answer
20 views

How to add all combinations of two pairs of key-value to a table?

I have a database of items, as each item has a set of key-value pairs such place => london field => engineering since I frequently need to find items having the same two key-value sets. I ...
0
votes
0answers
28 views

MS SQL Server 2012 - How To Find Last Executed Queries

I have 120+ databases and I want to find last executed queries (actually I want to find last executed queries and last executed select queries) in last 10 days. I have below scripts for this job: 1-) ...
0
votes
1answer
12 views

Query processor ran out of internal resources and could not produce a query plan” - MariaDB

i have some query SELECT COUNT(loan_payment) as setcolumn FROM table1 WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'} and period_code = 'MONTHLY' AND company_id =...
0
votes
1answer
26 views

Error “ORA-0091:Invalid Character” when executing a PL-SQL statement in Oracle [closed]

I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of ...
0
votes
1answer
15 views

Using count on two coumns yields same values in both columns (Postgresql) [closed]

I am looking at agent call data and want to calculate how many agents worked at a certain time bracket and how many calls where done in the same time bracket to get an average call volume per agent ...
-1
votes
1answer
13 views

Find closest follower in a rank list

On my database I have the table named Rank_List_T: Rank Name Time 1 John 110 2 David 120 3 Bob 121 4 Hans 124 5 Claus 126 6 Gustav 140 I am interested in the person, who ...
0
votes
0answers
14 views

MySQL - Query for obtaining record when 2 columns match 2 or more values at the same time

I have this database that I got from this post that manages products and its variants: +---------------+ +---------------+ | PRODUCTS |-----< PRODUCT_SKUS | +---------------+ +-------...
0
votes
0answers
12 views

how to query a bank database to find if deposit was modified [closed]

Retired 15 years after DBA for hospital. I have reason to believe that a deposit I made has been modified. Bank of course denies it. DA will issue subpoena but needs to know what to ask for. Is ...
0
votes
0answers
31 views

Checking if data exists in database [closed]

I have this query to check if any of the seal numbers exist, if not then it update the row; however it's asking to supply value for previous seal. I understand the error but if I'm checking if it ...
0
votes
1answer
20 views

How do I get data from multiple tables if the agency_code doesn't match

I have a table with these fields Employment_Form (table name) employee_number full_name agency_code The second table is HumanResources(table name) employee_number full_name agency_code The ...
0
votes
1answer
34 views

List Top Daily Rentals Per Customer using a CTE

I've been trying to learn CTEs and having a bit or trouble getting the hang of them. I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. ...
0
votes
1answer
27 views

Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

I'm having a bit of trouble wrapping my head around a way to make this JOIN work. Postgres 11.5 I have two tables: Table1: customer_id date (various other fields) Table2: customer_id date (...
0
votes
0answers
47 views

How to count references to fields given the definitions of views?

I have a bootstrapping script which defines views to be exposed to the data users in public schema. It filters out sensitive fields from the tables in private schema, also makes various aggregations. ...
0
votes
1answer
16 views

How to get all column names of a table in in multiple rows

I have used the following to just view the column names of a table (without any values) Is there a way to view all column names in one column but separate rows? Maybe do a lateral join? Open for ...
0
votes
1answer
32 views

Select from other table if exist

hi I want to union 2 tables but I am not sure if the table is exits this is my query: select a from table union select case when exists(select a from table1) then (select a from table1) else (...
0
votes
0answers
27 views

How to move columns into row in oracle

I want to query customer and cars table and show the result as above
1
vote
1answer
22 views

SQLite where condition - compare two last rows

I have a SQLite DB table like this: [Stock_price_Table]: +-----+-------------+--------+------------+--------+--------------+----------------------+ |code | date | first | open | close ...
2
votes
1answer
51 views

Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema: This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if ...
0
votes
0answers
17 views

Same server, same query, different response time

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * ...
0
votes
0answers
21 views

Delete interlinked tables data using one query

I want to write the database query to delete the data from multiple interlinked tables. Below is the structure of the database: CREATE TABLE TABLE1 ( ID NUMBER(10), TABLE2_ID NUMBER(10), ...
0
votes
2answers
27 views

Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL

Hope you're doing well I have a table with the structure below : Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date datetime) Some Example Data : ...
0
votes
1answer
14 views

Querying the most recent records with two fields: datestamp and timestamp

I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate ...
0
votes
0answers
19 views

SQL Server use of Full Text Index when using equal expression in where clause instead of contains

Can Microsoft SQL Server use Text Index when supplying the where clause in the format of select * from TABLE1 where TEXTCOLUMN1='UNIQUETEXTVALUE1' We have a process flow engine that generates ...
0
votes
0answers
9 views

MySQL - Year Over Year - Query Syntax

Experts I am working on a query to get Year over Year data comparison from current year to prior year. For example, if current year is Jan 1st - now, I would calc that dynamically in my query but ...
-1
votes
1answer
41 views

Returning only 1 row that meets some criteria

This is an issue that I run into quite frequently, and which I would love to know how to work through. I'm writing a report/query that is simple enough, but when run, returns rows with duplicate ...
0
votes
1answer
37 views

How to select from table by comparing multiple rows with same key

I have below table KEY_STATUS. || KEY || STATUS || | 0001 | FAILED | | 0001 | COMPLETED | | 0002 | FAILED | | 0003 | COMPLETED | | 0002 | FAILED | | 0004 | STARTED | I would like to ...
-1
votes
1answer
26 views

Query to find a customer which has the most orders by value?

I am tasked with finding the customer in the database with the most placed orders by value. I was thinking about tackling this problem like this: select ime,priimek, count(stranka_idstranka), sum(...
0
votes
1answer
10 views

How knowing queryid get query string in Postgresql?

I try use postgres_exporter. It monitors postgres and sends metrics to prometheus. It has a request https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml#L137 The output will be a ...
0
votes
0answers
34 views

Optimise the selection of two distinct column pairs from the same table

I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014 I have the table structure specified below. I want to select two column pairs: (SourceID + ...
0
votes
0answers
9 views

Combine information on multiple rows with the same key, into one row with the information in the cells next to it in a query in microsoft access

basically what this guy did here: Combine multiple row's records in one row with MS Access Query? but in such a way where its from: Case Number / Insurance 00001 / BCBS 00001 ...
0
votes
2answers
42 views

How to improve multiple case when query performance?

I have the following query which counts when the event_time is equal to the day of the month. I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status....
0
votes
2answers
30 views

Mysql horizontalizing grouping

I have a table that essentially has rows with: sessionId date/time event event can be (to make it simple, "start" and "end") The same sessionId however can be reused, so, simply I have sessionId ...
1
vote
1answer
45 views

How to get for a percentage here in below query?

A small change: report for each year the percentage of movies in that year with only female actors, and the total number of movies made that year. SELECT Year, COUNT(*), (COUNT(*)*100/(...
0
votes
1answer
69 views

Speeding up “duplicate” checks

We have an events table that is reasonably large (~35 million rows) The original table is like this, CREATE TABLE [dbo].[Events]( [EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [...
0
votes
2answers
32 views

How to select the most recent entry based on a field in MySQL

My table has multiple entries by individual customers, These customers have made multiple purchases over the years from any of several suppliers listed in my database. I need to run a query to select ...
1
vote
0answers
17 views

Dax Formula for Calendar

I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula? dDate = CALENDAR(DATE(YEAR(MIN(Ftransactions[...
0
votes
1answer
16 views

Skip Immediate Next Cordinate

I have a table foo where x, y represents coordinates How do I discard the immediate next coordinate while using select? x | y | value ------+-----+------- 32 | 10 | 7 32 | 11 | 2 ...
0
votes
0answers
78 views

nested set - Get ID from last sub entry with MySQL

I need the last Child item ID from this nested set table So i would need ID 4,10,11,16 or "rgt" values in this Example What i found is just the query below. THis is what the table looks like: +----+-...
0
votes
1answer
62 views

How to calculate a value for all rows in sql server?

I have two tables. One is a linking table to handle a many-to-many relationship between users and construction projects, and the other one is a payment table which includes the users' payments. Now I ...
0
votes
1answer
26 views

Trying to understand how to use postgresql generate_series() for this operation

First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence This is the old ...
0
votes
1answer
39 views

Explain shows index doesnt working but i have an index

The query in MySQL is: EXPLAIN EXTENDED SELECT id, username, name, last_name, IF(friend IS NOT NULL,1,0) as friend, activated, if(private=1 AND IF(friend IS ...
0
votes
2answers
16 views

How to find number of products having SALE price 50% of MRP using MYSQL?

Given below table(Product) id|name|price_type|price| --|----|----------|-----| 1|book|MRP | 10| 2|book|SALE | 5| 3|pen |MRP | 10| In this table the answer is 1, since ...
0
votes
1answer
46 views

Can I Make This Query Faster?

I have this SQL query: SELECT parent_category FROM category JOIN ( SELECT category_id FROM venue JOIN ( SELECT venue_id FROM city_venues WHERE city_id = 3 ) AS venues ...
-1
votes
1answer
26 views

Using session variables to generate GROUP information

We want to know whether the given approach is correct or not. I understand that there might other and presumably better and more optimized ways of accomplishing the task. But it is a curiosity that I ...
-1
votes
1answer
21 views

SUM first consecutive rows only SQL Server

I have the following data: ROW SEGMENT ORDEN LONG 1 39 11 49.00 2 39 10 102.00 5 39 7 104.00 6 39 6 139.00 7 39 5 97.00 11 39 1 ...
0
votes
1answer
24 views

Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

I have a table lets say calendar Table Calendar --> idCal (int) , numberOfFields (int) And each Calendar has asigned a number of fields Table Field --> idField(int), textField(text), idCal (...
0
votes
2answers
26 views

How can i make month appear with two digits SQL Server

how can i make monthid column values appear as example: 201901, not 20191. INSERT INTO [dbo].[table2](accountno, monthid, active) SELECT accountno, CONCAT(YEAR(InsertionDate), month(insertiondate)), ...

1
2 3 4 5
18