topic Re: Translations from T-SQL: TOP 1 OUTER APPLY or LEFT JOIN in Data Engineering

Translations from T-SQL: TOP 1 OUTER APPLY or LEFT JOIN

MattHeidebrecht — Thu, 05 Dec 2024 18:21:12 GMT

Hi All,

I am wondering how you would go about translating either of the below to Spark SQL in Databricks. They are more or less equivalent statements in T-SQL.

Please note that I am attempting to pair each unique Policy (IPI_ID) record with its highest numbered Location (IL_ID) record. There can be many Location records for each Policy record. The Location table links to the Policy table via Policy.IPI_ID = Location.IL_IPI_ID.

I have tried to utilize LIMIT 1 in certain ways (example further below) but either receive errors or the results do not match.

Any help or suggestions are appreciated!

T-SQL:

select ipi.IPI_ID ,loc.IL_ID from Policy ipi outer apply ( select top 1 il.IL_ID from Location il where il.IL_IPI_ID = ipi.IPI_ID order by il.IL_ID desc ) loc -- select ipi.IPI_ID ,il.IL_ID from Policy ipi left join Location il on il.IL_ID = ( select top 1 il2.IL_ID from Location il2 where il2.IL_IPI_ID = ipi.IPI_ID order by il2.IL_ID desc )

Errors out in Databricks Spark SQL:

select ipi.IPI_ID ,il.IL_ID from Policy ipi left join Location il on il.IL_ID = ( select il2.IL_ID from Location il2 where il2.IL_IPI_ID = ipi.IPI_ID order by il2.IL_ID desc limit 1 );

Re: Translations from T-SQL: TOP 1 OUTER APPLY or LEFT JOIN

filipniziol — Thu, 05 Dec 2024 19:46:07 GMT

Hi @MattHeidebrecht ,

You can use ROW_NUMBER window function to order rows and then filter on that rank.

Here is the query:

WITH loc_ranked AS ( SELECT IL_IPI_ID, IL_ID, ROW_NUMBER() OVER (PARTITION BY IL_IPI_ID ORDER BY IL_ID DESC) AS rn FROM Location ) SELECT ipi.IPI_ID, loc_ranked.IL_ID FROM Policy ipi LEFT JOIN loc_ranked ON ipi.IPI_ID = loc_ranked.IL_IPI_ID AND loc_ranked.rn = 1;

Here is the end to end example with sample data:

CREATE OR REPLACE TEMP VIEW Policy AS SELECT * FROM VALUES (1), (2), (3) AS t(IPI_ID); CREATE OR REPLACE TEMP VIEW Location AS SELECT * FROM VALUES (1, 10), (1, 5), (2, 100), (2, 90), (3, 300), (3, 250), (3, 100) AS t(IL_IPI_ID, IL_ID); WITH loc_ranked AS ( SELECT IL_IPI_ID, IL_ID, ROW_NUMBER() OVER (PARTITION BY IL_IPI_ID ORDER BY IL_ID DESC) AS rn FROM Location ) SELECT ipi.IPI_ID, loc_ranked.IL_ID FROM Policy ipi LEFT JOIN loc_ranked ON ipi.IPI_ID = loc_ranked.IL_IPI_ID AND loc_ranked.rn = 1;

The result:

Re: Translations from T-SQL: TOP 1 OUTER APPLY or LEFT JOIN

MattHeidebrecht — Fri, 06 Dec 2024 15:22:57 GMT

Thanks filipniziol! I'll start running with that when I run into cases where I need an embedded TOP 1.

Re: Translations from T-SQL: TOP 1 OUTER APPLY or LEFT JOIN

filipniziol — Fri, 06 Dec 2024 16:18:55 GMT

Hi @MattHeidebrecht ,

Great! If this resolves your question, please consider marking it as the solution. It helps others in the community find answers more easily. 😊