People who code: we want your input. Take the Survey

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
-2
votes
1answer
13 views

Difference between these two query implementations of inner joining a table with a lookup table?

I've been learning querying and I've come across the concept of inner joining Where this query inner joins the columns departmentId from employee and department tables and displays the value of ...
-3
votes
0answers
19 views

can this query considerably well in performance? [closed]

Those select statement are good with GROUP_CONCAT or can this be better improvised like in what areas. I am new to writing queries. Thanks in advance. SELECT c.*, o.orderId, concat('[', ...
2
votes
2answers
50 views

Ordering streets by their names and polysyllabic numbers in SQL query

In Oracle DB there is a table with some data: LAG HNR STREET 00020 44 Aachener Straße 00020 44/2 Aachener Straße 00020 44/1 Aachener Straße 00020 46 Aachener Straße 00020 46/6 Aachener Straße ...
-1
votes
1answer
18 views

How to get last date from one column and sum of another column in one query? [closed]

I have a table name payments where there is payment_date, amount, and duration. I want sum of the duration column(i.e total duration in table) and the last payment date where amount > 0. duration ...
0
votes
2answers
30 views

Postgresql split single row into multiple rows based on date [duplicate]

In my case, we have a table ABC with the supposed 1 row. id name start_date end_date 1 Rahul 01/01/2021 06/01/2021 but while retrieving I would like to get multiple rows where the start date is ...
0
votes
0answers
17 views

Insert checking in another table if value exists

My problem is the following: I have 2 Tables T1(idName) and T2(idName, name). I need to do an Insert in T1 with only the name and that the query compares if the name exists in T2 and get the idName to ...
0
votes
2answers
35 views

Postgres Case-Insensitive search for Jsonb column tag keys

I have a requirement to obtain the rows which match given tags key and value case-insensitively. Here: Key search should be case insensitive and values may be String or Array of String. Right Now, I ...
-1
votes
0answers
14 views

ORA-01858: a non-numeric character was found where a numeric was expected [migrated]

I am beginner in oracle and need help regarding following query: select admin_id, to_date(date_created, 'DD-MM-YYYY'), name, mobile, email, (select status from status where status.status_id = admin....
0
votes
1answer
37 views

handling primary key with large tree, best way to get root id from child table X

I always used database design using this pattern; root table with PK id1 child1 table with PK id2 FK id1 child2 table with PK id3 FK id2 child3 table with PK id4 FK id3 etc... so if I have to get ...
0
votes
1answer
32 views

Postgres Query Array Column with Array

I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these ...
3
votes
1answer
44 views

Database Unallocated Space - Correct way to measure

I am confused over the web regrading few scripts on calculating the database size and free space within same. Most of the scripts over the web uses sys.databases_files dmv to calculate free space like ...
0
votes
1answer
20 views

lock and get next n records from table in oracle

I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS PHONE_NUMBER PHONE STATUS 1 U 2 O 3 U U stands for used , O is open/UnUSED . in my input I will get n as input ...
0
votes
0answers
10 views

MySQL - Select Records that matches all values [migrated]

If a table "XYZ" contains two columns as combine primary key, I need to select records that contain both Value1 and Value2 in COLUMN_B ( Result: ID4 ) I tried with "IN" query but ...
2
votes
3answers
135 views

How to solve error “ORA:01-006 :bind variable does not exist” when working with bind variables?

I have a table with below structure and data : create table TEST_TABLE ( item_number NUMBER, item_name VARCHAR2(50), item_col VARCHAR2(50), item_pol VARCHAR2(50) ) Sample data: ...
1
vote
2answers
40 views

Get all rows where specific column value occurs more than once (filter out single occurences)

I am trying to filter all sessionIds that occur once out of an existing result set. This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent ...
-1
votes
0answers
28 views

moved to partitioning, now cannot see old data (only current month) [duplicate]

We just partitioned a table by month using log_date as the indicator (it contains time series data). I can see each month partition file on the disk and they are large (some are around 48gb) However, ...
0
votes
0answers
40 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
0
votes
2answers
48 views

Get the highest parent id recursively in MySQL

I want to find the uppermost parent ID for each entry. For example, CREATE TABLE t1 ( ID int(11) unsigned NOT NULL, ParentID int(11) unsigned, PRIMARY KEY (ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
1
vote
2answers
42 views

ON DUPLICATE KEY UPDATE in INSERT INTO SELECT with GROUP BY

I have the query of INSERT IGNORE INTO t2 (ID,P1,P2,Items) SELECT ID,P1,P2,SUM(Items) FROM t1 GROUP BY ID,P1,P2; Since I need to run this query frequently to inspect changes in t1, I need ...
0
votes
1answer
25 views

general design pattern for smushing rows into one column

I have a table of people : id, person_name I have a table of tags: id, tag_name there is a junction table for the tags that connects people and tags: id, person_id, tag_id I have a query to get a ...
0
votes
1answer
26 views

Fetch all rows and leave column value empty if no equality match

I have a query where I want to fetch all rows where the value in column x is between 400-600. Including in this I want a matching column value from antoher table. But I want all 200 rows between 400 ...
0
votes
2answers
19 views

Count results of table by date [closed]

I am trying to query the number of transactions that occur on a given day with the following query select COUNT(seq_no) from "table" where start_time >= '2020-04-01 00:00:00' and ...
0
votes
1answer
23 views

What is the word that means a query predicate can use an index [duplicate]

I remember reading in a paper a word that means “query predicate can be evaluated using an index” but I can’t recall the word. For example if you had a table with a column called “name” and you had an ...
-1
votes
2answers
47 views

How to get a column from a table that corresponds to another table [closed]

I have these tables: OrderDetails: Orders: Employees: Products: Customers: I want to write a query where I can get the last name of the employee with the largest order quantity. I managed to ...
1
vote
0answers
12 views

How to design a query to pop up input with two text boxes in MS Access

I ask this question while I could not find any similar question neither on Google nor DBA Stackexchange, this question might be duplicated. I wrote this query in MS access to check for first name and ...
0
votes
1answer
8 views

issues with subquery sql

Hi im getting this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. this is my current SQL query: DECLARE @UgpEntry VARCHAR(50) SELECT @...
0
votes
1answer
36 views

How to count the reads on index in MySQL

I have a table with index. CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_MFGR CHAR(25) NOT NULL, ...
2
votes
2answers
172 views

Get a parent and the children using the parent name

Is there a way to get a parent and its children using a single query? CREATE TABLE `servers` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `parent_id` int DEFAULT NULL, ...
0
votes
0answers
30 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
69 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
65 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
644 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
43 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
17 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
89 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
21 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
25 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
40 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
42 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
38 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
31 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
48 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
50 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
45 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
31 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
58 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
36 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 ...

1
2 3 4 5
23