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
9 views

How can I ORDER BY the "most relevant" entries in this context?

I have this query: SELECT * FROM "my bookkeeping" WHERE ("timestamp" > $1::date - INTERVAL '7 days' AND "timestamp" < $1::date + INTERVAL '7 days') AND (&...
0
votes
2answers
21 views

SQL Query to remove associated records that all don't meet certain condition

I have the following tables and values in a 2014 SQL Express Server. They have been simplified to focus directly on the issue I'm having and for brevity. Tbl_Contact -ContactID Tbl_Opportunity -...
2
votes
0answers
47 views

How to reduce the cost of a nested loop in a PostgreSQL query

PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012. My table uniteobservation has 824 605 rows: CREATE TABLE activite.uniteobservation ( gid serial NOT NULL, numunobs character varying(20), typunobs ...
-1
votes
0answers
16 views
0
votes
2answers
24 views

Using nested json_arrayagg with two table which have many to many relationship

Consider following two tables in mysql database role(role_id); task(task_id); and a pivot table role(role_id,task_id) I can extract a single role in JSON format using the following query select ...
1
vote
0answers
18 views

limit user's SELECT query to a fixed number of rows

i have read the following questions: Limit Postgres results for a user and also noted the recommendation of limiting a user's statement_timeout, however i would like something a little more consistent....
0
votes
1answer
17 views

Postgresql: most common values in array column

I have a table which looks like this: post_id tags --- ---- 1 {'tag1','tag2','tag3'} 2 {'foo','tag3', 'tag1'} 3 {'bar','tag3','anothertag'} ... Tags is an array column....
0
votes
1answer
21 views

update datatype field int to decimal128 in mongo db in nested json structure

Hi have tried to updated nested field datatype db.test.find().forEach(function(data){ data.age = NumberDecimal(data.age); data.phone.$.mob = NumberDecimal(data.phone.$.mob); db.test.updateOne({_id:...
0
votes
0answers
49 views

How to retrieve min/max values and corresponding dates in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data: CREATE ...
-4
votes
0answers
25 views

Stock quantity Trigger in MySql [closed]

I want a simple pl/sql code that works on phpmyadmin to do the following. I have two tables, the first is called products and the second is called order. The two tables have a column with the name of ...
0
votes
1answer
47 views

How to find last value from Ms Sql

I have an example table like this: ID UserId Name 1 00009 Jomy 2 00012 Tomy 3 00012 Tomy 4 00012 Tomy 5 00009 Jomy I want to get the last value of UserId = 00012, (UserId from ID number 4). I ...
0
votes
0answers
13 views

Mongodump with 2 queries

I have two queries: var idsList = db.getCollection("collectionA").aggregate([{ $match: { createDate: { $gte: new Date("2022-01-01T00:00:00"), $lt: new Date("2022-...
-1
votes
1answer
39 views

How to query likes and user details for posts?

I'm pretty new to the world of databases and SQL. I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
4
votes
2answers
201 views

How to retrieve the maximum value and its corresponding date in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data CREATE TABLE ...
0
votes
1answer
20 views

Creating a parent table from child relationships

I have a child relationship table as CREATE TABLE ItemRelationships ( Item1 int(11) unsigned NOT NULL, Item2 int(11) unsigned NOT NULL, INDEX(Item2), FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON ...
0
votes
1answer
36 views

Postgresql: ILIKE query with % as search term

I have a JDBC query that performs an ILIKE operation. select project_name from project_table where project_name ILIKE '%test%'; The above returns all the results matching (wildcarded) the term test. ...
-1
votes
0answers
18 views

Table name, Table size, its description, date it was created in Mongodb

I want to Create a list of tables that take up the most amount of storage, include description of what it is, table name, and size, a date that explains how far back we have historical data in mongodb ...
-2
votes
1answer
78 views

Named parameters in queries for SQL Server

ALL, I'm trying to find a proper syntax to do the following: SELECT a, b, c FROM foo WHERE foo.id = :id; (the query above is written as in SQLite). The "normal" query would be written as: ...
-3
votes
1answer
43 views

Select records based on priority

I have data stored as: TestId | Priority | --------+-----+----- test103 | 2 | test104 | 2 | test105 | 1 | test106 | 0 | test107 | 1 | test108 | 2 | ...
0
votes
0answers
22 views

Faster JOIN using LIKE

I have a simple tag table as CREATE TABLE Tags ( TagID int(11) unsigned NOT NULL AUTO_INCREMENT, Tag varchar(255), ParentID int(11) unsigned, Parent enum('Yes', 'No'), UNIQUE INDEX(Tag), INDEX(...
2
votes
2answers
126 views

How can I grant permission to multiple databases / tables to a user in a single query in MySQL?

I am trying the query - GRANT SELECT ON company.*, school.*, onlineshop.* TO user1@localhost; To which I am getting a syntax error. I can grant permissions one by one using - GRANT SELECT ON company.*...
0
votes
1answer
19 views

Mongo DB Query does not give desired result

I have two collections in mongo db. One called list with keys id and movieid in it. One called movies with movieid and Genre in it. The results I am trying to retrieve are: Which listid has the ...
0
votes
0answers
21 views

How to find a document with the highest value of a certain property?

Here is a sample of the collection I have in MongoDB called list { "Id" : 1, "Name" : "Music", "PicId" : 1 }, { &...
1
vote
1answer
53 views

SQL query display # of events per day between two time ranges

I have the following table: id start_date end_date status ---------------------------------------------------------------------- 1a 2020-04-09 21:34:00.000 ...
4
votes
3answers
131 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
14 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
30 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 ...
1
vote
1answer
51 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
49 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
43 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
37 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
31 views

Command basics, look through three tables [closed]

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
58 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
27 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
26 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
21 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 ...
0
votes
3answers
39 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
7 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
56 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
244 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
43 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
51 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
42 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
51 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
101 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
115 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 ...

1
2 3 4 5
25