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
7
votes
3answers
1k views

Improper use of logical operators has led to poor query performance

I have a table with huge amount of data (Nearly 15 million) and below structure. create table test (a int,--> /* There is a normal index on this column */ b int, <other columns>) There is a ...
0
votes
2answers
552 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 ...
-2
votes
2answers
43 views

joining table 1 with table 2 where there are multiple matches in table2 and you want 1 but definitely not the other

Here's a simplification of the problem... suppose you have a bunch of socks in a drawer. Each sock has an id associated with it, but they might be different striped colors. Let's say the stripes are ...
0
votes
1answer
105 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
52 views

Optimal index for a nested query?

I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance. The table contains > 2 Mio rows counting. I am working with Oracle. ...
1
vote
1answer
54 views

PostgreSQL query for parent - child relations

I need to store objects in a PostgreSQL database version 13. Each object has an ID, a name, occupies a level and belongs to a parent object, relation denoted by belongs_to. The values for the levels ...
1
vote
2answers
725 views

SQLServer - Multiple PIVOT on same column

I have a table data like the below with Type Regular/Irregular from January to December. I need a query to do this data like the below table: January with below divided into two-jRegular & ...
0
votes
0answers
13 views

Query for getting all data in a particular timeframe for the respective agent_id

Currently I'm using CodeIgniter and MySQL to retrieve a count of data within a particular timeframe. My table data looks something like this: Where there are different agents who are changing ...
0
votes
1answer
49 views

Counting number of records newer than X date matching a condition quickly

I have a table with 100 million rows. These are transactions for different accounts. A single account may have more than 500,000 transactions. Here is basically what I'm working with: CREATE TABLE ...
0
votes
1answer
1k views

How to select recursively in a child parent design situation (in MySQL)?

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) ...
0
votes
0answers
18 views

SQL Query Statement with Group By

I am trying to analyze how far players of the game progress as a function of age. The age is measured as the number of days since game installation. For active players who played the game at a certain ...
1
vote
2answers
67 views

Select * - Why or Why Not?

Is there really any difference between these two queries? SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table] SELECT TOP 1000 * FROM [database].[schema].[table] ...
0
votes
1answer
18 views

Column collision in Left join with many columns vs PHPMyAdmin resultset

I have 4 tables (75 columns each table approx) but they share columns with the same name: Table Master_Producto Master_Product.PD_Prof_21 Table Product_Structure Product_Structure.PD_Prof_21 I am ...
-2
votes
1answer
39 views

query to get start time and end time from same column based on boolean value order by primary key [closed]

As of now, this is my current query: SELECT MAX(CASE WHEN status = 1 THEN [timestamp] END) as machineOn ,MAX(CASE WHEN status = 0 THEN [timestamp] END) as machineOff FROM machine_table where ...
0
votes
1answer
38 views

Unnest array values from multiple columns with dynamic unpivot

I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns. Example input: CREATE TABLE tbl_wide ( p_key integer PRIMARY KEY,...
-1
votes
1answer
32 views

How to pass a parameter into a subquery in a join statement [closed]

I have a query that should be implemented in an MSSQL View so no local variables allowed, this query is working but it has to call a function to return a temporary table, this function is called ...
0
votes
0answers
15 views

Sql query + wpml in one language only

I'm not a developer, just a normal user, so please don't expect me to be like you :) I'm managing my website in WordPress, Running 4 languages using WPML, (en,de,cz,pl) My concern is, I want to search ...
0
votes
1answer
213 views

SQL Percentage of users falling in a specific range

I have a list of users with corresponding date of birth. Is there any possibility I can get a percentage of users born between 1990 and 2000 in a single SQL query? The query I tried is as follows: ...
-1
votes
0answers
12 views

How to find names which are having same mobile numbers given table structure as follows

Output should contain A,B, D,E as phone number for A & B is same . Similarly phone number for D & E is same.
10
votes
4answers
31k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
0
votes
1answer
49 views

how to get result value JSON and send result Format JSON

I have Query for result JSON but I need result like this: result:[ "B":{1628424359816,35}, "A":{1628424359816,1}, "B":{1628424380816,63}, "...
0
votes
1answer
177 views

How to collapse contiguous single day start/end date ranges in MySQL

(edited - I oversimplified the original problem) I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data: INSERT INTO ...
0
votes
2answers
34 views

LIMIT the number of rows in GROUP BY and GROUP_CONCAT with ORDER

In this sqlfiddle, CREATE TABLE t1 ( id int(11) unsigned NOT NULL, val int(11) unsigned NOT NULL, rank mediumint(7) unsigned, INDEX(id), INDEX(rank), PRIMARY KEY(id,val) ) ENGINE=InnoDB DEFAULT ...
-1
votes
1answer
48 views

mysql vs mariadb query [closed]

Hope i don't make any mistakes posting here :) Hi everyone, i'm using the following query on mysql and works perfect. But when i use the same query (same database) on a mariadb i'm getting wrong ...
0
votes
1answer
16 views

select time intervals from a nested query

I have a table which has the following columns: id, start_timestamp, device_id I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps. For ...
-2
votes
1answer
45 views

Find a doubled value between two columns in the same row

We discovered a bug in our program where there are some instances of a doubled value. I am trying to write a query to find all instances of this doubled value. Right now I am using WHERE (GRIR....
0
votes
1answer
38 views

SQL query working with 8.0 but not working with 5.7 [closed]

I have a mysql table like this CREATE TABLE `users_search_activity` ( `ID` bigint(20) UNSIGNED NOT NULL, `user_id` int(11) NOT NULL, `country_id` int(11) NOT NULL, `search_keywords` text ...
0
votes
2answers
53 views

Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?

I have a table as you can see below: create table table_test (col_id number(1)) The table has below data: col_id ------ 1 2 3 I create a view based on this table: create view v_table_test as ...
0
votes
1answer
39 views

Joining without a unique identifier

Payments PaymentRef Product PaymentAmount MerchantRef P01 ABC 100 MR01 P02 ABC 200 MR02 P03 XYZ 200 MR03 Refunds ReundRef Product PaymentAmount MerchantRef R01 ABC -10 MR04 R02 ABC -100 MR05 ...
0
votes
1answer
325 views

Substitute DISTINCT ON for query transformation (Postgresql to Redshift)

I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt: In my final output, I would like only to keep rows with the highest rank (in green). And ...
0
votes
0answers
66 views

How to use PIVOT or LISTAGG to solve this?

I have a table like below. USERID USERNAME ITEM 12345 JESSE ITEM1 12345 JESSE ITEM3 12345 DON ITEM2 12346 DANNY ITEM4 12346 ELISA ITEM1 12347 ANDY ITEM2 12347 ANDY ITEM4 What im trying to ...
2
votes
2answers
724 views

How to count decimals without zeros

How can I count all decimal place up to the first zero For example I have a column named x with an entry of numeric datatype value 1.955000000 I want to count the decimal places without the zeros. ...
-1
votes
1answer
47 views

how to select date time step by 60 second from first value to last value in table

I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval like this datetime value 2021-05-02 12:20:1 5 2021-05-02 12:20:15 8 2021-05-02 12:...
1
vote
0answers
42 views

JOINing two mapping tables

I have two mapping tables as CREATE TABLE ProjectDevelopers ( DeveloperID int(11) unsigned NOT NULL, ProjectID int(11) unsigned NOT NULL, INDEX(ProjectID), PRIMARY KEY(DeveloperID,ProjectID) ) ENGINE=...
0
votes
2answers
91 views

Scheduling - First or Next Available Appointment with MySQL

I am looking for first available appointment with all doctors or next available appointment based on the given date. Tables:- doctor_availability --> This is to store doctors availability for all ...
0
votes
1answer
73 views

oracle pl/sql block throwing ORA-00922: missing or invalid option

I have the following segment of oracle pl/sql block DECLARE rec all_tab_columns%ROWTYPE; v_tableName VARCHAR2(100); v_columnName VARCHAR2(100); v_schemaName VARCHAR2(100); BEGIN ...
0
votes
3answers
34 views

How can I return multiple columns from an array of ids?

I have a two tables like the following: workshops table +----+--------------+------------+ | id | name | option_ids | +----+--------------+------------+ | 1 | Conversation | {2,3} | +----...
1
vote
1answer
18 views

Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]

The query in question is the following: SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3) ERROR: pq: operator does not exist: integer = integer[] Basically the ...
0
votes
0answers
29 views

What establish a session?

As an analyst I'm running multiple queries on our Oracle ERP via Power Query (Power BI). Now, I hit some form of session limit on my default profile's session limit. How do you evaluate how many ...
0
votes
1answer
68 views

Executing a query with a variable in a loop

I have two select, how can I do the same using a loop and a variable in PL SQL Developer. I have: select 1 as nm from dual; select 2 as nm from dual; I need something similar: DECLARE x NUMBER; BEGIN ...
1
vote
0answers
10 views

all_views is not showing a view even I have access to the view

I'm having the following issue: This select does not retreive anything: select 1 as exist from all_views where view_name='FND_USER_RESP_GROUPS' and owner='APPS' and rownum=1; But If I execute this ...
1
vote
2answers
557 views

MySQL do not accepts wildcard for tables name level grant

Trying to grant a user select privilege on multiple tables using wildcards as following: GRANT SELECT ON 'DBname'.'foo_%' TO 'username'@'%' IDENTIFIED BY 'password'; But keep giving the following ...
0
votes
1answer
53 views

Fill down if no value in period, autoknow the periods

I have created a query that gets the running total from my general ledger by period. This works fine, however as soon as there are no transasctions in a period, my query will return noting in that ...
0
votes
2answers
34 views

Finding groups which have two values in another column

Given the following sample rows: MTYPE RNO VAL S 1 V1 S 1 V2 S 1 V3 R 1 V4 S 2 V5 S 2 V6 R 3 V7 R 3 V8 How can I list groups of RNO which has both 'R' ...
0
votes
2answers
92 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
348 views

Nested push in Mongoose

I have a user schema like that: name: String, nick: String, email: String, password: String, bio: String, color: String, followers: [String], followings: [String], posts: [{ author: { ...
0
votes
2answers
39 views

Limit and offset (pagination) for rows with related data (with joins)

I have tables Alpha and Beta. Beta belongs to Alpha. create table Alpha ( id int auto_increment primary key ); create table Beta ( id int auto_increment primary key, alphaId ...
-3
votes
1answer
54 views

Count only new people added to payroll based on previous month

Every month we have new people added or removed from payroll. i'm trying to figure out how many new people are added based on the previous month. The image show an example of how the table is ...
-4
votes
2answers
28k 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
480 views

MySQL date range if data not available display 0

I've a table here here is the structure : id,id_berita,ip_user,tanggal I try to use this query : select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00'...

1
2 3 4 5
24