All Questions
12 questions
0
votes
2
answers
73
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 ...
- 101
2
votes
0
answers
628
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 ...
- 131
3
votes
2
answers
204
views
Can subqueries produce different execution plans than CTE's?
I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test:
...
- 2,650
1
vote
1
answer
38
views
Very weird result of CTEs and subqueries [closed]
WITH t1 AS (SELECT a.id,
AVG(standard_qty) std_avg,
AVG(poster_qty) pos_avg,
AVG(glossy_qty) gloss_avg
FROM accounts a
...
- 13
2
votes
1
answer
1k
views
Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [closed]
I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think):
with d AS(
SELECT
...
8
votes
1
answer
1k
views
How can I get the hierarchical values from the below query?
I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.
I need to get all the category IDs and their subcategory ...
- 471
8
votes
3
answers
16k
views
No advantage of using Cross Apply or CTE over inline sub-query
I came across a query like this:
SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i
I changed it to following
;WITH cte_count
AS
(
SELECT COUNT(1) c, OrderId FROM ...
- 287
1
vote
2
answers
56
views
Join Two queries with different declarations
I need to join two different queries to get some columns from both. The first one has a CTE.
Query1:
WITH StatusChanges AS
(
SELECT
HistoryID,PartID,Status, DateAdded,
...
- 13
0
votes
1
answer
843
views
Select records as groups based on sequence and date
I have data stored as:
CompID | Seq | Udate | Utime
--------+-----+-----------+-------
mx433 | 1 | 1/1/2012 | 13:54
mx433 | 1 | 1/2/2012 | 13:43
mx433 | 2 | 1/4/2012 | 14:23
mx433 ...
- 1
8
votes
3
answers
44k
views
Check if any of the values is in a subquery result
I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
- 83
1
vote
1
answer
6k
views
Match multiple components of a subquery CTE in PostgreSQL
I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
- 13
1
vote
1
answer
801
views
Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]
I have the following query:
SELECT
COUNT(*) AS `count`
FROM
agreements
WHERE
start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y')
AND
start_date < STR_TO_DATE('15-01-2015','%d-...
- 21