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

Oracle and stopkey of an index range scan?

I am struggling to resolve a performance issue with an Oracle query that is opting to perform a full table scan of a table with several million records. The table is partitioned by month of a ...
1
vote
1answer
429 views

subquery must return onl one column in postgresql

I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can ...
0
votes
2answers
68 views

Strange behavior of TOP 1 clause sql server

I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set. select person_id, visit_id, ...
4
votes
4answers
925 views

Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
-2
votes
2answers
65 views

How to Cast today data as bigint to Data - 202104171830

I have got today's data as Bigint: 202104171830. How could I cast this Bigint value to today's date ? Please help. MSSQL
2
votes
0answers
19 views

how to combine like and between?

i got tables like this: data_transaksi tgl_trans ket sim_wajib what i want to do is showing data from certain dates(tgl_trans) and ket values (values = SW,SS and penyesuian nilai tambah) code ...
2
votes
2answers
91 views

How to make large "in" queries in C# for an indexed field in MSSQL?

In mssql, how can I run effectively this query: select id from table where field in (gigantic list of ordered longs) Table schema: table: { id primary key field long } index findex (field) I ...
0
votes
0answers
23 views

Fastest way of updating a spider web of data

I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
0
votes
1answer
39 views

Query Store System View JOINs

In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
0
votes
0answers
17 views

SQL for finding composite key elements with same field [duplicate]

I have a table with a composite key (a,b) wherein I want to return the first key and the count of the second key that have the exact same set. For example if we have: a | b ------- 01 | 2 01 | 3 02 | ...
0
votes
0answers
49 views

PostgreSQL: Query runs fine in Prod but really slow in QA

Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
-1
votes
2answers
49 views

A quick way to ORDER all IDs randomly in MySQL [closed]

I need to assign random IDs to a table. Thus, I create a mapping table as CREATE TABLE t2 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, SourceID int(11) unsigned NOT NULL, UNIQUE INDEX(SourceID), ...
0
votes
2answers
362 views

mySQL subqueries calculating winning percentage

I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table: id result dice1 dice2 player_id 1 LOSE 4 4 9 2 LOSE 4 5 7 3 ...
0
votes
0answers
13 views

Find nested related users

From this SQL schemas, I want to find all users related by contest.id I try this req : SELECT u.id, u.firstname, u.lastname, u.avatar FROM contests c LEFT JOIN trials AS t ON (t.contest_id = c.id) ...
0
votes
2answers
40 views

How do I find out whenever a value changes in a certain column?

Need help if anyone can offer! I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about ...
0
votes
1answer
238 views

Find partial match with textsearch query

I have the following query that I did hard looking on this site: SELECT * FROM renns WHERE ( to_tsvector('english', coalesce(numar_admin, '')) || to_tsvector('english', coalesce(...
0
votes
1answer
52 views

Repairing buggy query server-side – without changes in the app

More external apps are accessing our ERP SW database. As we were adding some columns into our structure, some queries from the external app become buggy because they are not specifying the targeted ...
0
votes
2answers
116 views

Optimizing nested SQLite query

I have a SQLite table named hashbands that looks like this: hashband | file_id | window_id ------------------------------ potato | 0 | 0 potato | 1 | 0 The table has 100M+ rows (lol). ...
0
votes
0answers
41 views

Sum of column per distinct ID and group by different column

Let's say I have the following table: create table orders (id int, order_id int, vendor varchar, price int, quantity int); INSERT INTO orders values (1, 1, 'nike', 10, 2), (2, 2, 'nike', 10, 3), (3, ...
1
vote
3answers
68 views

How to write "and" queries for a lookup table when you can't use the AND keyword?

I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want,...
0
votes
3answers
52 views

select one row based on a string

I have a table like this: and I want to have the output like this: If I use case to this, I'm not getting a unique value for each pc. I used: case when software like '%7zip%' then software else 'no' ...
0
votes
1answer
147 views

Replace all column values with other values in the same column according to primary key

I'm querying the following table: id value applicable id1 5 Unknown id1 5 Unknown id1 6 Yes id 2 5 Unknown id 2 5 No id 2 3 Unknown I would like to replace all "Unknowns" in the ...
12
votes
4answers
3k views

In the same query INSERT, can two VALUES being NOW() return a different time?

I met a query similar to INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW()) having column definitions | Created | datetime | | Updated | datetime | Will in this case MySQL ...
0
votes
1answer
50 views

Grouping closer times in SQL Server

I want group closer times (time gap should not exceeded 2 min). What I mean is to group the records which have a time gap less than 10 min. And return min and max time of each group. How it is ...
0
votes
1answer
100 views

Join three tables with max date value

I'm trying join three tables: hydrants ----------------- fid | h_number | ----------------- 1 | 2525 | ----------------- hydrats_survey ---------------------------------------------- fid | ...
0
votes
1answer
62 views

MySQL Use an In Clause With a Comma Delimited Column [duplicate]

I am trying to use a column that has a comma delimited list of values in an in clause. I thought about doing a contains but it would not be correct when searching for single digit values. Example: 1,...
0
votes
1answer
30 views

Getting Indirect parents

I have following different tables LeafChilds ChildId, ChildName 1, Child1 2, Child2 Group GroupId, GroupName 1, Group1 2, Group2 3, All GroupChildMapping GroupId, ChildId 1, 1 2, 1 2, 2 ...
0
votes
1answer
104 views

postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Lets say that tableA has column columnA with jsonb data in it like the following: { "A": 5, "B": 15, "C": 20 } and tableB has columnA with jsonb data in it like ...
1
vote
1answer
75 views

3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

The First Question is: "List the name of each trip that does not start in New Hampshire (NH)." SELECT TripName FROM Trip WHERE State!='NH'; /* works with State='NH' */ The Second Question ...
0
votes
2answers
185 views

Comparing Query Performance Across DBMSs

I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL. I have been looking into command line tools for each system to get the ...
2
votes
1answer
39 views

MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker)

I have three tables, one that lists regions, one that lists hotels and another that lists reservations. The hotels table has a foreign key for the regions table, to show which hotel is within which ...
0
votes
0answers
18 views

How to query with maximum number of rows with same value?

In Mariadb 10.5.8 I have a table with some repeating value e.g. Item , qty shoes 3 pants 2 pants 2 pants 4 shoes 3 pants 2 shoes 6 pants 2 shoes 3 pants 2 I want to query up to 3 occurrences of each ...
3
votes
2answers
60 views

Select rows with the highest value for one ID, after grouping by two IDs

It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount. SELECT I.AgentID, I.PersonID, ...
2
votes
1answer
126 views

How can I update values based on the value from the previous day?

I have a MySQL database temporary table with a bunch of meter readings in it against a system ID and a date (which combined make up the primary key). Some entries have NULL for the meter reading, in ...
0
votes
1answer
173 views

Repair Database using .FRM .IBD .OPT Files ( Hard copy mysql folder )

I had a crash on my server. The only thing I could do was hard copy the mysql folder Now I am trying to access my database again through the PHPMyAdmin interface. Some databases (mostly WordPress) run ...
0
votes
2answers
311 views

MariaDB: Insert records (list of tuples) into table if not exists

Currently I have several sql queries that look like this: INSERT INTO `test` (col1, col2) SELECT * FROM (SELECT '1','2') AS tmp WHERE NOT EXISTS ( SELECT * FROM `test` WHERE col1='1' AND col2='...
3
votes
4answers
68 views

Concatenating only desirable values with LISTAGG

In the Oracle DB there is a table with data "YEARS", see a table/image below +-----+-----------+------------------+ | FID | I0 | BJA | +-----+-----------+-----------------...
0
votes
1answer
68 views

Create cache table [closed]

I need help with SQL Server. There is a task scheduled to run an .exe file. After the exe finish execution, I can get the logs below: 2021-03-05 22:45:00 : Create cache table 2021-03-05 22:45:00 : ...
0
votes
1answer
34 views

Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL

I have a source table with below structure: create table customer_info (customer_num number, birth_date char(10)) Unfortunately the birth_date column data type is char(10) instead of date. ...
1
vote
2answers
47 views

Under what circumstances would these 3 queries return different number of results?

Hello I have the following question: Query 1: SELECT b.id,     (     SELECT SUM(cb.invoice_amount) AS sum     FROM payment_lines cb     WHERE cb.batch_id = b.id     ) AS batch_total_invoice_amount ...
0
votes
0answers
24 views

What syntax does PostgreSQL require when all identifiers in a query are dynamic? [duplicate]

Example: SELECT $1 FROM $2.$3 WHERE $1 = $4 Result: ERROR: syntax error at or near "$2" Example: SELECT "$1" FROM "$2"."$3" WHERE "$1" = "$4&...
0
votes
2answers
74 views

MSSQL query to select top 2 values [duplicate]

I am not from a DBMS background and I couldn't find much help from reading some resources once. I have a requirement like: There are 5 interfaces with some execution time. My goal is to find the top ...
0
votes
0answers
34 views

In what sort of way "v$transaction" view will help DBA's to improve the performance of the query?

I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:...
1
vote
1answer
50 views

T-SQL: Assign identifier to block of results

I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each ...
0
votes
1answer
22 views

Having problems joining table

I have the following tables and I want to know Jack's dob, salary and the total score of that employee who is also a player. The relational schemas are: Emp_Players (eid,pid,team) composite key is (...
1
vote
2answers
226 views

MySQL how do I priorities single column by multiple keywords

I have a column of product title, I have to order by result by max word's matched from entered string Select Id, tittle, description,....., Case When a.title REGEXP [[:<:]]Men's shoes[[:>:]] ...
0
votes
2answers
60 views

Postgresql get rows based on minimum value

Having this table: warehouse_id destination_id days 1 1 2 1 1 3 2 1 3 2 1 4 3 1 5 3 2 5 1 2 2 2 2 3 I'd like to get the warehouse_id and days for each value of warehouse_id, where the row ...
1
vote
1answer
16 views

Return only records with a date after the most recent September 1st

I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the ...
1
vote
1answer
31 views

How to insert rows for each of the type column?

How to insert rows for each of the type column? If there is two type, type 1 and type 2, then I need to insert two rows and also need to change the order and id value for whole table. current status: ...
0
votes
1answer
126 views

Debit Credit Balance calculating from previous records with Pagination MYSQL

I have table with the following schema and demo data- id date particulars debit credit 104 29-12-2020 Crediting NULL 1000 105 29-12-2020 Purchased Item:A ...

1 2 3
4
5
25