Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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).

6
votes
1answer
50 views

Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables? I suspect not, but I ...
0
votes
3answers
34 views

Get distinct category columns with multiple subcategory columns mapped in table

I have 2 tables, category and subcategory, this query select C.Description,s.Description from category C inner join SubCategory S on C.CategoryID =S.CategoryID where C.IsActive=1 and S.IsActive=1 ...
-1
votes
0answers
25 views

Multiple rows with identical IDs to different columns - do I need to pivot? [on hold]

There are multiple records in my database for a single ID. I want to get only a single value from duplicate data and single different value in the next column with the same ID. Do I need to use PIVOT ...
0
votes
1answer
35 views

Find multiple rows that share one field value but have one unique value

I have tables cards, decks, and a join table card_decks. I want to find the count of a unique set of pairs of cards where the a given attribute is true. I feel like I'm close but there is another step ...
0
votes
0answers
7 views

Select based on list of pairs

I have a table with two main columns, id1 and id2: drop table if exists pairs; create table pairs (id1 int, id2 int); insert into pairs values (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8); I ...
0
votes
1answer
20 views

Create a generated column from the last 100 occurrences for a given user ID [on hold]

I have Three tables, Table1 stores post engagement rate (rate_new_1) for Social media posts (shortcode) Table2 stores post information including taken_at_timestamp the third table Stores information ...
1
vote
0answers
11 views

Mongodb throwing an error when I run the query to record last position from oplog

I am getting an error when I run the query in the secondary member to record the last position from the oplog. below is the query. db.oplog.rs.find().sort({$natural:-1}).limit(1).next().ts and the ...
0
votes
1answer
31 views

Is it possible to exclude rows from a select based on conditions of another row

My data set is quite simple; Ref Value Voucher No ------------ ---------- ---------- M019922 -154.80 100081968 M019922A 154.80 100081968 M019922B -154.80 ...
0
votes
1answer
12 views

Postgres : Select items for an interval from the max date in store

I have a database with items looking like this : id , item_id , date_item , price 0 , 3232 , 2019-04-22, 10 0 , 3232 , 2019-04-23, 4 0 , 3232 , 2019-04-24, 1 0 , 3232 , 2019-04-...
0
votes
1answer
20 views

Combining nested query three levels not showing, all columns

I have the following query. Let's call it Q1. SELECT t3.staffing_id, Max(t3.date_action_taken) AS MaxOfdate, IIf(Month(Min([t3].[date_action_taken])) Between 4 And 6,"Q1", IIf(Month(...
0
votes
0answers
30 views

Convert MS Access SQL Query to PostgreSQL Query [closed]

I'm having trouble converting this MS Access Query to standard SQL for PostgreSQL. I've looked into the differences here: https://weblogs.sqlteam.com/jeffs/2007/03/30/quick-access-jet-sql-to-t-sql-...
1
vote
2answers
32 views

SQL queries to get the duplicate records based on trim

I have a table that has got the below set of records. UNMASK MASK 123 897609 00123 896509 0000456 5632789 456 32567889 5678 5632789 From the above table I need to select only the below records....
0
votes
0answers
35 views

IIF and Min SQL in a query [closed]

I have the following query: SELECT Tracking.staffing_id, IIf(10<Month(Min([Tracking].[date_action_taken]))<12, Datepart("m",(Min([Tracking].[date_action_taken]))),"Q1", "Q2") AS ...
-1
votes
2answers
75 views

Select ONLY Max(date) when there's repeating dates

Here is a sample of the data contained in my Tracking table: tracking_id date_action action_id staffing_id ------------- ----------- ----------- ----------- 1 2019/03/04 4 ...
0
votes
0answers
42 views

How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 ...
0
votes
0answers
17 views

How to list tables and columns using in select query or view PostgreSQL? [closed]

I have many views in PostgreSQL and I want to report all tables and columns that they touch.
1
vote
0answers
43 views

Can I see failed SQL queries text?

I added some triggers to third party DB and now the third party application failed to run. Is there a way to see the failed queries? for example: I run: SELECT * FROM Table_1 --query succeeded ...
0
votes
2answers
28 views

select columns with different conditions

This question focus to the COUNT result 1st query will show the COUNT with date condition. 2nd query will show the COUNT without date condition. Now I need a query which make the COUNT result in ...
-1
votes
0answers
27 views

Oracle loop all columns in all tables in a specific schema and count not null rows

I have a number of databases where I need to loop all columns in all tables to find not null values in each column. For example: Input: +----+---------+---------+---------+---------+ | ID | ...
4
votes
1answer
110 views

Generate wide table from lookup values

I have a wide table that looks like this: Table Windspeed Timestamp A-004 A-005a A-006a ... (column 161) E-009 09-Apr-19 00:00:00 13.09 13.95 10.16 11.71 09-Apr-19 ...
2
votes
2answers
64 views

SQL Preserve the Order in group by

I have records like below in my table. I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not ...
-2
votes
0answers
31 views

sql query for previous day data only [closed]

i am using a simple sql query to extract data from previous day. select * from bg_summaries where register_tt like '05-MAY-19%' order by ID; So instead of changing the date, is their an ...
0
votes
0answers
37 views

AWS Aurora queries much slower in “sending data” phase than RDS instance

I've been using an RDS instance during the development phase of a project. This was a db.t2.micro instance, so not very powerful at all. Today I have created a new Aurora (5.6) [serverless] database ...
1
vote
1answer
66 views

SQL Query to look for nulls from two different tables while adding in a new column that states what table its from

I need help with a query that I am trying to make. I have two tables, Orders and Process and I am trying to find instances where the OrderName and ProcessTitle in each table is null and I want to add ...
0
votes
1answer
25 views

How to count unique records within a time range in MySQL?

I have a table with the below definition: CREATE TABLE visit_log ( id INT NOT NULL , door_entry TIMESTAMP NOT NULL); This table has the below records: insert into visit_log values(1, '2009-04-...
0
votes
0answers
17 views

How do I use nth_value to grab the 2nd purchase date when querying from line items?

Say for example I have query like so: select t1.customer_id, nth_value(t2.created_at, 2) over(partition by t2.customer_id order by t2.created_at asc rows unbounded preceding and unbounded ...
3
votes
1answer
101 views

Large string concatenation in query

I am trying to concatenates strings in T-SQL like 'This is a test ' + CHAR(13) + CHAR(10) + ' string' When the string is long (I do not know the limit), I get an error: Some part of your SQL ...
0
votes
0answers
24 views

How to add values matching from table B to table A?

I've been struggling for a while now. Can someone help me? I have 2 tables. I have a table 'SKUS_info' with SKUS(a number assigned to a product), approximately about 30k rows. And the second table ...
0
votes
1answer
19 views

How to use LIKE operator with multiple conditions at once in PostgreSQL

I want to select records using LIKE operator with multiple conditions, I can use OR between the conditions but I am looking for a short way to solve this issue. I am trying the following query: ...
-1
votes
0answers
29 views

SQL Server + Eloquent conversion datetime in query/results [migrated]

In my project I am using Eloquent as db layer. And I have big issue with conversion datetime object. There are 3 servers in play: SQL Server Production (PHP) Local develop Production and local ...
0
votes
1answer
43 views

How to JOIN with missing rows?

I need to JOIN two tables to correct the price with exchange rates as SELECT date, price/rate AS adjusted FROM table1 a JOIN table2 b ON a.date=b.date The problem is that table2 does not have the ...
0
votes
2answers
58 views

How to get results in separate rows after joining two tables

I have two tables say - Plans and Discounts. Below are the sample data in Plans table. Below are the sample data in the Discounts table. These two tables are related using PlanName column. I am ...
0
votes
1answer
38 views

ON CONFLICT ON CONSTRAINT fail postgresql

INSERT INTO O_USER (ID, NAME, LASTNAME, EMAIL, USERNAME, PASSWD, DATE_REG, DATE_UPD, DATE_DEL, IS_DELETE, TIPO, FTP) VALUES (%s,%s,%s,%s,%s,%s,DEFAULT,NULL,NULL,%s,%s,%s) ON CONFLICT ON CONSTRAINT ...
0
votes
0answers
25 views

MySQL Query to Display Organized by Date

I'm good with basic types of queries but this has me stumped. I have the table schema listed below, I need display the results in a table, with each row displaying the available results for a given ...
1
vote
1answer
25 views

Create Trigger that updates the same table before (or after ??) the Insert

I have just started using triggers and one thing that I would need to do is to create a trigger that updates the same table that's inserting the row. TABLE: CREATE TABLE `city` ( `city_id` ...
0
votes
0answers
9 views

How to always select records in array or empty array

I have a field records of jsonb[] type and I need to insert records from other tables(has many relation) whenever there is an update via trigger. When I apply select SELECT INTO records * FROM "...
0
votes
1answer
47 views

I do not perform SUM with conditions in SQL Server

I want to make SUM of a colummn according to various conditions. In this example for the column 'fac_saldo_tot' if value is greater than 500 add the column and group them down the column '...
0
votes
1answer
10 views

Help with optimizing Oracle Query - nested with count

I have 2 tables (Students and Cellphone). Columns in Students: StudentID, FNAME, PHONEID Columns in Cellphone: PhoneID, Phonename I want to create this: • Run a query to show the most popular ...
0
votes
1answer
17 views

Check and count if a column value is used in another column of same table

I have a table like this and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0,...
0
votes
0answers
39 views

SQL Server group by date, time between status

I have a table that looks like the following: id_usr dt_event Sequence 0000001 2015-08-01 14:57:21.737 1 0000001 2015-08-06 15:34:21.920 2 0000001 ...
-3
votes
1answer
44 views

Fastest Aggregate function when you have series of duplicate values

Frequently, I'll need to crunch down a table into a series of aggregates. And frequently, it will look like this. John, Smith, Houston, Texas, Order 55, Balloon, 99 dollars John, Smith, Houston, ...
0
votes
1answer
20 views

Query optimization: how to efficiently occlude overridden records?

Imagine a table with roughly a couple of billion rows. A simplified schema for the table would be started_on: datetime recorded_on: datetime image_id: integer It's indexed by started_on. One ...
0
votes
2answers
31 views

How can I create such a query in MS access so that only customers with more than 3 bookings show up?

I have two different tables, the "customers" tables, with "customer ID" as the primary field and a few more fields such as Name, Surname etc. (All in text format). The second table is "Booking ...
-3
votes
1answer
25 views

Using a calculated column in a SQL Select statement

I am trying to write a query. Column A comes from database, column B is either 1 or -1 and it is result of a CASE statement that looks into the same table. Now, my column C needs to refer to column B ...
0
votes
1answer
28 views

MySQL - Maintain History of Records but Using Most Recent

Let's say I'm developing a database for a transportation service which owns certain vehicles and hires drivers which are assigned to specific vehicles. The database comprises of the following tables: ...
4
votes
2answers
222 views

Extract rows of a table, that include less than x NULLs [duplicate]

I am working with a SQL Server database, which includes a lot of NULLs. To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2). My ...
0
votes
1answer
35 views

Select query with nested relationships

I have the following 3 PostgreSQL tables: wallet: +----+--------+-----------+-----------+--------+ | id | name | createdAt | updatedAt | locked | +----+--------+-----------+-----------+--------+ | ...
0
votes
1answer
34 views

Proper creation of a database with categories that have sub-categories

Preface: I have for the most part never worked with databases, and am new. So my terminology may be incorrect at times. I was wondering what the correct procedure would be for creating a database ...
0
votes
2answers
39 views

Block certain queries from being logged in slow query log

I have mysqld_node running on my mysql server to get statistics to be used by grafana + prometheus to graph dashboard for this MySQL server. However, i noticed that 90% of the queries in my slow query ...
0
votes
1answer
28 views

How to search records based on date range for other matching data

I have below records in table APP_No Stage Start_Date End_Date A123 L1 01-01-19 02-01-19 A123 D1 01-03-19 05-03-19 A345 L1 01-01-19 02-01-19 A345 D1 01-03-19 05-03-19 I want ...