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
1answer
37 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
167 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
28 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
45 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
15 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
39 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....
-3
votes
0answers
32 views

Help me solving this database question

Given two relations R and S, where R contains M tuples, S contains N tuples, and M >N > 0, give the minimum and maximum possible sizes (in tuples) for the resulting relation produced by each of ...
1
vote
1answer
37 views

SQL query working with 8.0 but not working with 5.7

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
52 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
37 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
77 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 @...
-5
votes
0answers
24 views

Fetch data from detail table referring to two master tables

I have below tables:- I.tblOwnerDetails Ownerid ownerFirstname Lastname 1 Abc A 2 Xyz X 3 Pqr P II.tblListMaster Listid Listname 1 List1 2 List2 III.tblListDetails Detailid Listid Ownerid 1 ...
0
votes
1answer
293 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
54 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
701 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
46 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
77 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
41 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
31 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
17 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
28 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
57 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
491 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
51 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
323 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: { ...
-1
votes
0answers
21 views

Mysql SELECT query for getting available rooms in a building

I have a problem to write MySQL SELECT query in which I can get list of free rooms in a time slot with a specific date range. So I am using two tables named spaces and bookings spaces: Bookings: I ...
0
votes
2answers
36 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
27k 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
456 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'...
0
votes
0answers
39 views

Oracle 19c Select Query with Database Link

How can I use the following query with database link: set pages 999 col "Size MB" format 999,999,999 col "Objects" format 999,999,999 col OWNER for a25 select obj.owner "Owner&...
0
votes
2answers
514 views

MySQL - Get counts of `car models` by `car make`

I have a table with cars data, i.e. Makes, Models, & Year. I want to get the count for each Car with how many models it has... something like the image below: This is the SQL query I am using ...
0
votes
1answer
21 views

Creating a daily Search Query for the previous day to today

I am trying to make a custom search query in maximo for equipment at work. I have a specific time range I want but it is always for the day before. Example: Today is 8/26/2021. I want a query for 8/25/...
-1
votes
1answer
65 views

PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. Sleep Table Here is the select statement I have so far: select wakeuptime,hour from ...
1
vote
1answer
23 views

Can I order/group on a column and show a limited number of the results at the top and then show the remainder in a second sort order?

I have a simple table for product data with a column called special which can be 1 or 0, indicating if the product is special and should be highlighted in listings of products that I need to generate. ...
0
votes
2answers
72 views

Query Times Worsen on 1 Server Vacuum/Analyze on Postgres 9.6

We have a query which is called approx 15,000 times during the day. The query itself should take a few milliseconds to run. I like to think we have the appropriate indexes in place for the query: ...
3
votes
4answers
57 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
0answers
22 views

How do i design a schema with proper DB engine to accumulate data depending on this need on clickhouse or in any other database?

We're a new Adtech company and I was planning to design a database where I'll pull all the data to a single table and then make new tables with a materialized views for others to generate multiple ...
0
votes
1answer
47 views

How to calculate maximum and minimum values from different tupples in SQL?

I'm working on a program, that would "predict" the next possible tupple in a table. One of the features would be the finding of arithmetic progressions, there are different ways to do this: ...
0
votes
1answer
51 views

How to use PowerShell Invoke-Sqlcmd to query and table and update a PowerShell $variable?

How do I update a PowerShell variable using an Invoke-Sqlcmd statement? Eg. $PSVariable Import-Module sqlserver $QueryStatement = "SELECT $PSVariable = Max(Data) FROM MyDatabase" ...
1
vote
0answers
12 views

Container is running beyond physical memory and killed on request (code 143) [closed]

I ran a query which involved a JOIN and an AVG operation: SELECT AVG(user_amount) AS average_user_per_game FROM ( SELECT start_time ,end_time ,COUNT(DISTINCT ID) AS ...
1
vote
1answer
18 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
141 views

Set the file name in "SELECT ... INTO DUMPFILE"

I am trying to write a query to dump a single LONGBLOB data into a file. SELECT data FROM table WHERE id = 123 INTO DUMPFILE fileName which data, fileName, and id are the fields in the table. SQL ...
1
vote
1answer
54 views

Query to divide each column value to two categories: [Common , Not_Common]

I have a table with below structure and data: create table PTEST ( col_name VARCHAR(50), col_value VARCHAR(50) ) COL_NAME COL_VALUE ----------------------- first apple ...
1
vote
1answer
32 views

Can I split text from one column into to two existing columns in My SQL [closed]

SELECT `Appointment`, SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date, SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time FROM go_applicants ...
1
vote
1answer
25 views

query left join error

I have two queries that get me a correct result, but when I try to join they give me an incorrect result. I want to make something like: SELECT directoscount.parte, SUM(directoscount....

1
2 3 4 5
24