sec query result in to first table last row

Question

i want to show to the sec query result in to first queyy last column in sql.commen

Tuhin Paul · Answer

WITH RankedTable1 AS ( SELECT Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT ROW_NUMBER() OVER (PARTITION BY Year ORDER BY Settings_COT) AS RowNum1 FROM Table_1 ) RankedTable2 AS ( SELECT Year PerApp_Pot ROW_NUMBER() OVER (PARTITION BY Year ORDER BY PerApp_Pot) AS RowNum2 FROM Table_2 ) SELECT T1.Year T1.Settings_COT T1.Settings_DOT T1.Settings_POT T1.Monitoring_COT T1.Monitoring_DOT T2.PerApp_Pot FROM RankedTable1 T1 LEFT JOIN RankedTable2 T2 ON T1.Year = T2.Year AND T1.RowNum1 = T2.RowNum2 ORDER BY T1.Year T1.RowNum1; RankedTable1 : We apply the ROW_NUMBER() function to Table_1 based on the Settings_COT column (you can adjust the ordering based on your requirement). This assigns a unique row number ( RowNum1 ) to each record for a given Year . We use the PARTITION BY Year clause so that the row numbers are reset for each year. RankedTable2 : Similarly the ROW_NUMBER() function is applied to Table_2 based on the PerApp_Pot column (again you can adjust ordering criteria). This assigns a unique row number ( RowNum2 ) to each record for a given Year .

Sreenath Kappoor · Answer

Hi Lejo CREATE TABLE Table_1 ( Year INT Settings_COT VARCHAR(50) Settings_DOT VARCHAR(50) Settings_POT VARCHAR(50) Monitoring_COT VARCHAR(50) Monitoring_DOT VARCHAR(50) ); CREATE TABLE Table_2 ( Year INT PerApp_Pot VARCHAR(50) ); INSERT INTO Table_1 (Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT) VALUES (2025 'DIYAR [2501-86]' 'Finance [2501-87]' '2501-88 [2]' NULL NULL); INSERT INTO Table_1 (Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT) VALUES (2025 'DIYAR [2501-86]' 'Finance [2501-87]' '2501-98 [2]' NULL NULL); INSERT INTO Table_2 (Year PerApp_Pot) VALUES (2025 '2501-102 [2]'); INSERT INTO Table_2 (Year PerApp_Pot) VALUES (2025 '2501-90 [2]'); SELECT * FROM TABLE_1 Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT 2025 DIYAR [2501-86] Finance [2501-87] 2501-88 [2] NULL NULL 2025 DIYAR [2501-86] Finance [2501-87] 2501-98 [2] NULL NULL SELECT * FROM TABLE_2 Year PerApp_Pot 2025 2501-102 [2] 2025 2501-90 [2] Join the tables SELECT T1.Year T1.Settings_COT T1.Settings_DOT T1.Settings_POT T1.Monitoring_COT T1.Monitoring_DOT T2.PerApp_Pot FROM Table_1 T1 LEFT JOIN Table_2 T2 ON T1.Year = T2.Year Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT PerApp_Pot 2025 DIYAR [2501-86] Finance [2501-87] 2501-88 [2] NULL NULL 2501-102 [2] 2025 DIYAR [2501-86] Finance [2501-87] 2501-88 [2] NULL NULL 2501-90 [2] 2025 DIYAR [2501-86] Finance [2501-87] 2501-98 [2] NULL NULL 2501-102 [2] 2025 DIYAR [2501-86] Finance [2501-87] 2501-98 [2] NULL NULL 2501-90 [2] To avoid generating 4 rows when joining the tables due to a many-to-many relationship on the Year column you can create a one-to-one mapping by using the ROW_NUMBER() function. WITH RankedTable1 AS ( SELECT * ROW_NUMBER() OVER (ORDER BY Settings_COT) AS RowNum FROM Table_1 ) RankedTable2 AS ( SELECT * ROW_NUMBER() OVER (ORDER BY PerApp_Pot) AS RowNum FROM Table_2 ) SELECT T1.Year T1.Settings_COT T1.Settings_DOT T1.Settings_POT T1.Monitoring_COT T1.Monitoring_DOT T2.PerApp_Pot FROM RankedTable1 T1 LEFT JOIN RankedTable2 T2 ON T1.RowNum = T2.RowNum Result: Year Settings_COT Settings_DOT Settings_POT Monitoring_COT Monitoring_DOT PerApp_Pot 2025 DIYAR [2501-86] Finance [2501-87] 2501-88 [2] NULL NULL 2501-102 [2] 2025 DIYAR [2501-86] Finance [2501-87] 2501-98 [2] NULL NULL 2501-90 [2]