SQL add select query to another select query as a new column

February 14, 2024

I have two SQL select query. I want to add second select results to first query as a new column. How can I do?

My queries – first query:

SELECT 
    [Value], ColumnIndex, RowIndex + 1 AS RowIndex,
    Axis, SegmentationModuleType, ModuleType, VersionId, IsTotalValue FROM 
    FinalWeightCalculation
WHERE 
    Axis IS NULL
    AND IsTotalValue = 1 
    AND SegmentationModuleType = 'Choosing' 
    AND ModuleType = 'TSY02' 
    AND VersionId = 299 
    AND AccountGuid = 'f069b6d7-b2d1-4f36-b6a4-9d830f35de73' 
ORDER BY
    RowIndex

Second query:

select ROW_NUMBER() OVER (ORDER BY OrderNo) as RowIndex, Name from Criteria where VersionId = 299 order by OrderNo

>Solution :

You may join on the row number:

WITH cte1 AS (
    -- your first query
),
cte2 AS (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY OrderNo) rn
    FROM Criteria 
    WHERE VersionId = 299 
)

SELECT t1.*, t2.Name
FROM cte1 t1
LEFT JOIN cte2 t2
    ON t2.rn = t1.RowIndex
ORDER BY t1.RowIndex;