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

Perform TABLESAMPLE with WHERE clause in PostgreSQL

I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL. This runs fine: select * from customers tablesample system (1); But I cannot figure out how to ...
0
votes
0answers
21 views

Need help with indexes on 2 slow wordpress query

Here are my my 2 slow query i want to improve. SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN (525627,516360,525519,...
1
vote
1answer
35 views

Determine Order Streak

For a particular customer, I have data returned by a query that looks like this: OrderDate OrderCount 2020-01-12 0 2020-01-13 17 2020-01-14 22 2020-01-15 21 2020-01-16 30 2020-01-17 12 2020-...
1
vote
0answers
12 views

Deduplicate table based on createdate

I'm new to SQL and I'm trying to get my head round a lot of stuff but so far with little success. So I'm trying to get some help, here goes I have a table "Userdata" Email | FirstName | LastName | ...
0
votes
1answer
14 views

PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column?

I've a table, CREATE TABLE friends ( friend1 serial references users(id), friend2 serial references users(id), unique (friend1, friend2) ); Friends are in no particular order, but I ...
0
votes
1answer
21 views

MySQL query help

I need a query that returns the number of liked messages and orders them by the number of likes (from most to least). I have tried: SELECT users.user_id, messages.author, messages....
0
votes
1answer
36 views

How to show multiple record if has top 1

Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
4
votes
2answers
441 views

SQL Query returning same value while using where 1 condition

I have created a temp table and inserted the values as given below. create table #temp( val int ); insert into #temp values(333); insert into #temp values(222); insert into #temp values(111); On ...
1
vote
1answer
32 views

Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index CREATE INDEX tab_x_y ON tab(x) ...
0
votes
2answers
41 views

How to select all columns plus one alias column in MySQL

It is a simple question, but i could not find the answer. I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
0
votes
2answers
55 views

Running a query on the results of another query?

I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase For example, the ...
0
votes
0answers
25 views

Extract Image URLs from html text using Postgres sql Query

I have a column in postgres db in which html contain stored. Like this <p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
-1
votes
1answer
24 views

Delete Data in Two Tables Based on Date in One Table

I am trying to delete data in a table where a date field value is older than 30 days and also delete related records in a second table that has no date field based on a matching key column in both ...
0
votes
1answer
18 views

How can I find the difference between queries that return multiple rows?

I have two tables, website and page, that look like such: website id | name ------------ 1 | site 1 2 | site 2 3 | site 3 page id | name | site_id | deleted ---------------------------------- 1 ...
-2
votes
2answers
70 views

Coding questions [closed]

I have a question, which i can't solve. Here are 2 tables: Customers: | Credit_Card_ind | Credit | Account ID | |-----------------|--------|:----------:| | 1 | 1 | תל אביב | | ...
0
votes
1answer
64 views

Counting Null rows between non Null values

I have a table that counts logins to a website with the first column, USER_ID, being the user's unique identifier. The next column is a list of months in the date format %m/%d/%Y, and the next column ...
0
votes
1answer
16 views

Count total unique rows for each date and group by date

I have the following table: | email | date_added | ------------------------- | a@a.com | 2019-01-01 | | b@b.com | 2019-01-01 | | a@a.com | 2019-01-02 | | c@c.com | 2019-01-02 | | x@x.com | 2019-01-...
0
votes
1answer
25 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
votes
2answers
60 views

How to select only one row for each distinct value

I have a pretty long query which includes subqueries and joins that I need to run for a report. It runs without error, but the results show 11 rows for each value, when I need it to display 1 row per ...
0
votes
1answer
47 views

Distribute values equally

Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed. How to return a result set so a port does not get overloaded? One possible solution and set based ...
0
votes
0answers
45 views

Trying to understand the use of a '!' character

I stumbled upon this query that someone wrote and was curious as to what '!' was being used for here. SELECT ACC9.JPM_CAT_ITEM_ID, !a.accomplishment ACCE9.JPM_DESCR90, ...
2
votes
1answer
43 views

Finding stored procedures with XACT ABORT ON OR OFF

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
0
votes
2answers
19 views

Return all except some fields using findOne() in mongodb for node.js

I am trying to use the following script to query for a document but the returned document still contains the field (password) that I don't want. const client = await MongoClient.connect( ...
0
votes
1answer
18 views

Multiple query to columns indexed in MySql

I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure: +----------------+ | products | |----------------| | ...
1
vote
1answer
35 views

Postgres: how to limit the number of query results by aggregate type

Consider the following table definition CREATE TABLE associations ( id SERIAL PRIMARY KEY, resource_id integer, target_id integer, relation_id integer ); which is used for specifying ...
0
votes
1answer
34 views

How to get one row out of many rows with same column id

I have a use case in which I have three tables. Courses Table Course_ Id Name 1 MathMatics 2 Physics Teacher Table Teacher_ID Teacher_Name 1 AAA 2 BBB TeacherVsCourses ...
0
votes
2answers
88 views

SELECT last date from multiples values

I have this table: I want to make a query that allows me to show the last value in fecha_gestion grouped by NitSec. It would be something like this: SELECT id_log, fecha_gestion, nitsec FROM table ...
0
votes
2answers
44 views

Select fixed number of records/rows from growing table from SQL Server?

Background: A SQL-Server database table is used to store weekly reports of some system memory usage, meaning that each week a new table row/record/line is inserted. The question is how a fixed number ...
1
vote
2answers
67 views

From MySQL to MariaDB : bad performances issue with MyISAM tables

I'm currently migrating a Debian 6 MySQL server (5.1.73) to a fresh new Debian 10 MariaDB server (10.3.18). Please note that I'm not a database expert. All tables are under MyISAM engine storage, for ...
1
vote
0answers
26 views

Vote based approval system in DBMS

I try to create a system to auto approve proposed posts by popular vote. I am currently evaluating what portions of this I can do within the DBMS and if it is sensible. I use PostgreSQL and I can go ...
1
vote
1answer
31 views

Why is it important to carefully determine and select statistics for the query optimization process?

It's a general question rather than a db engine specific one. In popular db engines, we see the query optimization process taking statistics into consideration. I'm trying to understand what types of ...
0
votes
2answers
94 views

Mongodb Sort Array Objects within Array using expression

Please help to make query in mongodb. I want mongo query in which sort array objects using some expression. Example. I have documents like { "_id:"...
1
vote
1answer
24 views

How can I create a recursive query over a column that has gaps?

I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly. Given a table of games like: id | home_player | away_player | ...
0
votes
2answers
43 views

renaming result of query

i have two joined table in my postgres db tb_items and tb_colors id_item | group |id colors -------------------------- 1 a 1 2 b 1 3 b 2 4 c ...
1
vote
1answer
28 views

Need to create batches of 2000 records within a subset of records

I have a table with 200,000 records. One of the columns is a location ID. I need to break down the entire table in batches of 2000 but group them by the LocationID column. I have about 200 locations ...
-2
votes
1answer
27 views

What alternative sql method can I use to see more records, GROUP BY id only shows one row per unique id

What I want is per map id I want to be able to see the top highest scoring gangs (also I should be able to say from which date or a specific single day), (i thought of using GROUP BY ID, but it would ...
0
votes
1answer
33 views

Warehouse storage system (FIFO by BBD and date)

I have inventory table. CREATE TABLE inventory ( InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT, InventoryDate datetime, Quantity decimal(15,4), BBD date NULL, ItemID int(11) unsigned ...
1
vote
1answer
24 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
0
votes
1answer
32 views

How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

Here is an example of the json object rawJSON = [ {"a":0, "b":7}, {"a":1, "b":8}, {"a":2, "b":9} ] And I have a table that essentially looks like this. demo Table id | ...(other columns)...
0
votes
0answers
28 views

Union the same table. Optimize query

I'm coming with a question about merging two data sets. I am using STI (single table inheritance) in the products table, so one column defines what type of product the specific row stores. The ...
1
vote
2answers
41 views

Identifying number of rows (COUNT) that fall between two timestamps for each minute in the hour

Good day all! I'm attempting to write some SQL that can identify the number of rows that fall between two timestamps but iteratively count them up. An example use case, a telephone call CDR for one ...
0
votes
0answers
14 views

Is there any way easy to use eg. 50 or more values in queries , since manually putting values in query is time consuming

Is there any way easy to use multiple data (eg. 50 values) in queries import from some excel , since manually putting values in query is very tedious and time consuming.
0
votes
1answer
17 views

Combine multiples table to a new one

I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so. I have three tables that I want to combine in one using a common id column,...
1
vote
2answers
43 views

Find rows in a single table where values in a certain column are near-duplicates

I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization,...
0
votes
0answers
41 views

SQL query result table to an HTML table for email

please help me to figure this out. I need to send sql query results as html table. The challenge is that columns in this table are dynamic (most recent 5 dates). How can I replace literal column names ...
1
vote
1answer
20 views

Joining a table on an id but need to copy this data to other ids

I have an issue with a postgres database that I am not sure how to handle. These are the tables (simplified obviously) Users +--+---------+ |id|user info| +--+---------+ |10|{data} | |11|{data} |...
1
vote
2answers
126 views

How to print results in SQLcmd window

I'tm trying to show in SQLcmd window the results of these queries: SELECT SYSDATETIME() AS "date"; PRINT 'EXTRAÍDO POR:' SELECT SUSER_NAME() AS "Owner" PRINT 'PASSWORD SETTINGS'; exec ...
1
vote
2answers
27 views

Column not like any value in column in other table - function

The two tables in question are: completed_types id | type ---|-------- 1 | XXX 2 | YYY all_types: id | type | sub_type ---|-------|--------- 1 | XXX X | aaa 2 | XXX-Y | bbb 3 | YYY-X | ccc 4 ...
0
votes
1answer
27 views

Get the CreatedAt Timestamp when the number of rows is equal to some number

I have a simple table in sql server. id, OtherId, createdAt 1, A, 2019-11-28 05:03:03.983 2, A, 2019-11-28 05:03:06.937 3, B, 2019-11-28 05:03:12.983 What I want to retrieve is a timestamp when the ...
0
votes
1answer
44 views

scheduling medical appointment challenge

I'm working on a medical appointment scheduling system project. What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment? ...