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]

sec query result in to first table last row

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)