The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard
Questions tagged [cross-apply]
358 questions
4
votes
1 answer
OUTER APPLY in BigQuery
I want to apply a column to a list of users where I pull the latest sign in date .
Traditionally I would do something like this:
SELECT U.* , O.* FROM Users.Users U
OUTER APPLY ( SELECT .. FROM .. Events.Events E WHERE E.UserId = U.UserId)…
Agneum
- 727
- 7
- 23
4
votes
1 answer
Does CROSS APPLY WHERE clause work before cross apply or after on the results
I am doing a custom fuzzy matching algorithm that we need in one of our inter applications. I am trying to speed it up. When I do a cross apply against a fuzzy function to find suggested matches, I do not want to search unecessary data.
Here is…
Casey ScriptFu Pharr
- 1,672
- 1
- 16
- 36
4
votes
0 answers
SQL Server XQuery perfomance (nested loops)
I'm struggling with query performance. I'm having "flat" XML stored as XML in table. And I'm trying to extract all elements and attributes of that XML to some flat table. The issue is that I'm having ~200 columns/attributes to parse.
This is how it…
Marek Skolimowski
- 41
- 2
4
votes
1 answer
Convert T-SQL Cross Apply to Redshift
I am converting the following T-SQL statement to Redshift. The purpose of the query is to convert a column in the table with a value containing a comma delimited string with up to 60 values into multiple rows with 1 value per row.
SELECT
id_1
,…
user3254527
- 41
- 2
- 3
4
votes
7 answers
How to select top N salaries for each person?
I have the table
user_id salary month
1 100 1
1 150 2
1 200 3
1 180 4
1 140 5
2 10 1
2 40 2
2 20 3
2 15 4
2 45 5
I want to select top 2…
gstackoverflow
- 36,709
- 117
- 359
- 710
4
votes
1 answer
SQL Rank does not work as expected
Im trying to use SQL function Rank() to get a list the top records of several groups. Here is what im tring that does not work :
select hc.hId, hc.DpId, hc.Rank
from (
select d.hId, DpId, Rank()
OVER (Partition by DpId ORDER BY d.hId)…
Tjamat
- 193
- 1
- 11
4
votes
5 answers
How can I use Cross Apply to multiple rows?
I have this table :
;WITH cte AS (
SELECT Name='john' , Times=1
UNION ALL
SELECT 'paul' ,2
UNION ALL
SELECT 'george' , 3
UNION ALL
SELECT 'ringo' , 1
)
I want to display each row , Times times :
John 1
Paul…
Royi Namir
- 144,742
- 138
- 468
- 792
4
votes
2 answers
T-SQL CROSS APPLY with GROUP BY
I'm new to CROSS APPLY and trying to understand the mechanics of exactly how it works. Specifically, in doing some tests, I found that including a GROUP BY clause within the CROSS APPLY statement drastically improves the performance of aggregations,…
SQLDM
- 59
- 2
- 5
4
votes
2 answers
Guideline for SQL Server APPLY AND JOIN Keyword
I was reading an article on using apply & join keywords. See some SQL where one example uses inner join & other use apply keyword.
Here is table pic
SELECT E.EMPID, E.NAME, E.DEPTID, D.NAME
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON E.DEPTID =…
Thomas
- 33,544
- 126
- 357
- 626
4
votes
3 answers
CROSS APPLY too slow for running total - TSQL
Please see my code below as it is running too slowly with the CROSS APPLY.
How can I remove the CROSS APPLY and add something else that will run faster?
Please note I am using SQL Server 2008 R2.
;WITH MyCTE AS
(
SELECT
…
Etienne
- 7,141
- 42
- 108
- 160
4
votes
1 answer
Obtain maximum row_number inside a cross apply
I am having trouble in calculating the maximum of a row_number in my sql case.
I will explain it directly on the SQL Fiddle example, as I think it will be faster to understand: SQL Fiddle
Columns 'OrderNumber', 'HourMinute' and 'Code' are just to…
Alfons
- 147
- 2
- 10
3
votes
1 answer
Read xsi:type from T-SQL
I would like to read the xsi:type attribute from the "current" node in a SELECT statement. My XML looks like this:
merror
- 33
- 1
- 4
3
votes
4 answers
MySQL - Get Last Entry by Time (or first if Null)
I have the following SQL Server 2008 query:
SELECT T.*,Data.Value FROM [Table] T OUTER APPLY
(SELECT TOP 1 E.Value FROM [Table2] E
ORDER BY CASE WHEN T.TDateTime >= E.EDateTime then 1 else 2…
Simon
- 9,197
- 13
- 72
- 115
3
votes
5 answers
Cross apply boolean values in SQL
I had the table below which I need to create a Category column will store the boolean values as a category.
I would like to capture the Categories as a single column and I don't mind having duplicate rows in the view. I would like the first row to…
Christopher Kinyua
- 160
- 3
- 13
3
votes
1 answer
How do I create a T-SQL Cross-Apply with Entity Framework Core?
I want to create a query with Entity Framework Core which has multiple INNER JOINs and one CROSS APPLY.
I can create the INNER JOINs just fine but can't seem to make the CROSS APPLY work.
Did some Google searches bus nothing really useful came…
Bart Huiskes
- 71
- 1
- 7