Database

khaliloenit wrote:

I must have a sql or batch script

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

@ECHO OFF
FOR /F  %%i IN ('sqlcmd -S YourDBInstance -Q"set nocount on;select  CONVERT(DECIMAL(10,2),FVersion) from TVersion"') DO SET VNO=%%i

call .\v%VNO%\script.bat
@echo done.

Jörgen Andersson wrote:

without using procedural code

Mycroft Holmes wrote:

can't see the PK field

Jörgen Andersson wrote:

m going to try to do it using a recursive CTE.

1. Cross-join the two tables;
2. Generate the ranking value for each pair;
3. Generate a ROW_NUMBER, partitioned by the primary key of Y, and ordered by the ranking value (descending);
4. Select the rows where the row number is 1;

WITH cteRanked As
(
    SELECT
        X.PK As XPK,
        X.OtherColumnsFromX,
        Y.PK As YPK,
        Y.OtherColumnsFromY,
        RankingFunction(X.Value, Y.Value) As R
    FROM
        X CROSS JOIN Y
),
cteOrdered As
(
    SELECT
        XPK,
        OtherColumnsFromX,
        YPK,
        OtherColumnsFromY,
        ROW_NUMBER() OVER (PARTITION BY YPK ORDER BY R DESC) As RN
    FROM
        cteRanked
)
SELECT
    XPK,
    OtherColumnsFromX,
    YPK,
    OtherColumnsFromY
FROM
    cteOrdered
WHERE
    RN = 1
;