Questions tagged [subquery]

SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

Filter by
Sorted by
Tagged with
0
votes
1answer
80 views

Converting rows to columns using crosstab in PostgreSQL not working (relation “table” does not exist)

I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query. with checked_adgroup AS ( ...
1
vote
1answer
561 views

Having a subquery somehow do the same group by as the outer query

i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we'...
0
votes
2answers
20 views

Query to fine “Order_num” that has no two equal “product_name”

I have a table with below structure: create table A_TEST ( order_num int, product_name varchar(50), product_amount int, product_price int ); Sample data : insert into a_test (...
3
votes
1answer
698 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
0
votes
2answers
81 views

Delete the second record where a user has two records

I have a simple database scheme, which due to previously being badly set up has numerous duplicate records. record_table |--------------------------------------------------------| | id | subid | ...
2
votes
1answer
165 views

Single MySQL query with row averages based on conditions

I have problem, where i know how to proceed with multiple queries, but dont really now I Have a table with BP { name: varchar systolic : int diastolic : int timestamp: date } I need to ...
6
votes
1answer
72 views

Why is SQL Server spliting it's (JSON) response into multiple rows?

I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that ...
-1
votes
1answer
28 views

Write a dynamic query of nested subqueries [duplicate]

Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out: select DISTINCT e.display_name as &...
0
votes
1answer
30 views

Sql Group by on subquery rows

I have this table : ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret) Example : DateArret ||HeureDebut ||HeureFin ||EnumArret 2020-11-30 ||2020-11-...
0
votes
1answer
33 views

How to delete Specific row

http://sqlfiddle.com/#!9/df6245 How to delete all rows Where brand not equal to 107 for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it ...
1
vote
1answer
186 views

Logic of Assigning values to variables in Scalar-valued Functions

I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs. While troubleshooting my rewrites I came across some ...
2
votes
2answers
25 views

How to filters data with two windows conditions?

I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I ...
0
votes
1answer
80 views

PostgreSQL error: “subquery uses ungrouped column”

I have a query with subqueries that use only elements from the GROUP BY clause: SELECT DATE(cm.created_at) as "date", COUNT(1) as total_message, room_id, (SELECT ...
2
votes
1answer
26 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
0
votes
2answers
27 views

Independent aggregation of related entities, while also sorting by related (in a single statement)

I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
-4
votes
1answer
36 views

Error when running query as a subquery [closed]

The inner query runs successfully alone, but when I run it as a subquery, an error pop up. My query: Select TOP 3 * FROM (select fr.rating , sp.SpId ,sp.FName ,sp....
0
votes
2answers
37 views

Optimize duplicated but negated subquery

I have what I call a 'Hamlet' query in the form: select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
0
votes
1answer
282 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1answer
684 views

Calculating percentage over sub query SQL

I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign. First I calculated the ...
2
votes
1answer
19 views

SQL daily value (today value - yesterday value)

I have a database where there are sales . +----+-----------+------------+ | ID | Quantity | DATE | +----+-----------+------------+ | 1 | 40 | 2020/10/10 | | 2 | 0 | 2020/10/01 |...
0
votes
1answer
606 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
0
votes
1answer
733 views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces a result of ~...
0
votes
1answer
23 views

Using alias in Group by

I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are ...
4
votes
1answer
313 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-...
1
vote
1answer
481 views

Do too many subquries create multiple sleeping processes in MySQL?

I am using java & MySQL to make an app. When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed. I have read many ...
1
vote
2answers
3k views

MySQL Correlated Subquery (with multiple columns) per row

MySQL documentation states that: Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so ...
0
votes
2answers
199 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
2
votes
3answers
114 views

MySQL reuse select aliases

I currently have a query where I'm doing two subqueries to get X, Y data: SELECT t.series AS week, ( ... ) X, ( ..., AND ... ) Y, ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z FROM series_tmp ...
0
votes
1answer
50 views

Why is this postgres query ignoring the index in the subquery?

I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index: sophia=> explain analyze select count(distinct(source_doc)) from public....
3
votes
1answer
3k views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
33
votes
8answers
137k views

Get multiple columns from a select subquery

SELECT *, p.name AS name, p.image, p.price, ( SELECT ps.price FROM product_special ps WHERE p.id = ps.id AND ps.date < NOW() ORDER BY ps....
0
votes
2answers
1k views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
3
votes
1answer
69 views

Query optimisation

I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many Now I make one select from orders: SELECT "id" FROM "orders" WHERE "orders"."...
0
votes
0answers
11 views

pulling most recent results for each point based on date field

so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to ...
2
votes
1answer
11k views

Alternate to use LIMIT in subquery in mysql?

I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to ...
1
vote
2answers
198 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
0
votes
1answer
494 views

Query to find products with all the given attributes

I have a table in mySQL to store product attributes like this: ------------------------------------ | product_id | att_val1 | att_val2 | ------------------------------------ | 1001001 | x202 | ...
0
votes
2answers
172 views

Select/print data from subquery

In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user ...
7
votes
2answers
104 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
1
vote
0answers
45 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
-1
votes
1answer
53 views

MYSQL Error: Incorrect usage of UNION and ORDER BY?

Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries: SELECT * FROM( SELECT deal_page.id, ...
1
vote
2answers
105 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
0
votes
0answers
28 views

insert or update with summary subquery

i have a problem with follwing query INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
22
votes
1answer
88k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
0
votes
1answer
18 views

MySQL subquery with WHERE filters not working as expected

I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
1
vote
2answers
27 views

MySQL - GROUP BY not working with multiple CASE WHEN statements

I am working on a database with Kickstarter database, and I'm trying to: Define short, medium-lengthed, and long campaigns See how much each length of campaign raise Convert different currencies into ...
1
vote
0answers
15 views

subquery returns more tha one row

I want to update data of a table where data-name matches with another table and also one data are common with many items. For example, I have a cycle in which tyre is a part and quantity of tyre is ...
5
votes
2answers
142 views

Does Postgres optimize this JOIN with subquery?

In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO: SELECT po.id, SUM(...
0
votes
1answer
17 views

how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result

I have a table that has a jsonb column as 'result' which has an array of objects. Looks like this for every row: [{"grade": "A", "subject": "MATH"}, {"...
0
votes
1answer
13 views

Return average using same table data

I've got this query I'm testing with: select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average from ...

1
2 3 4 5
12