All Questions
Tagged with cross-apply t-sql
11 questions
6
votes
1
answer
563
views
Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
I've updated this post with some test data.
I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
- 61
3
votes
1
answer
1k
views
Add query hints to a CROSS APPLY [duplicate]
I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM sys.objects obj WITH (NOLOCK)
...
- 43.2k
3
votes
1
answer
9k
views
Performance improvement Outer Apply
I have a query which runs little slower.
SELECT b.BreakdownClassificationId,
k.IsinCode,
k.SedolCode,
ClassificationDate,
NAME,
InstrumentType,
...
- 1,399
1
vote
1
answer
2k
views
Issue with SUM OVER PARTITION BY in conjunction with GROUP BY
I am getting a typical group by error that one would see when a field is not in a group by or an aggregate in the select.
Column 'v.EDTAmount' is invalid in the select list because it is not ...
- 617
4
votes
1
answer
4k
views
While using Cross Apply, can I force "empty" records to appear?
I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS ...
- 617
1
vote
1
answer
109
views
Where and how to add selective totals in SSRS
I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to ...
- 617
1
vote
2
answers
413
views
TSQL help my feeble mind grasp Pivot/Unpivot on two columns
I've been trying to wrap my feeble mind around this one for some time, but I am having difficulty. I have looked at a few other examples like this SE.DBA answer, but I am having difficulty applying it ...
- 617
0
votes
1
answer
318
views
How to use reference to another table in aggregate function
I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:
SELECT [GroupId] = cr.[GroupId],
[...
- 333
7
votes
1
answer
1k
views
Cross Apply Fails When DateTime Added
In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
- 409
1
vote
2
answers
5k
views
How to keep row ordering with DISTINCT command or how to add row number to cross apply
I have problem that after distinct, order direction of Variable column change.
Here is example
Data Source
DECLARE @Table TABLE
(
FirstName NVARCHAR(255),
LastName NVARCHAR(255),
...
- 1,109
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