How can I prevent my query from throwing this error about a duplicate column?

Question

I'm trying to write a query that returns only one row for a certain combination of table data.

I have this query:

SELECT * 
FROM 
    (SELECT 
         *,
         ROW_NUMBER() OVER (PARTITION BY s.aaa_id ORDER BY s.Stats_ID DESC) AS Rank   
     FROM 
         dev.dbo.Stats s
     INNER JOIN 
         dev.dbo.Admin_X m ON s.main_id = m.main_id
     INNER JOIN 
         dev.dbo.Mod_Admin a ON s.aaa_id = a.aaa_id
     WHERE 
         m.early_reg_FLAG = 1 OR a.early_reg = 'Y'
    ) AA 
WHERE 
    Rank =1

But I keep getting this error:

The column 'main_id' was specified multiple times for 'AA'.

I know I am using a column called main_id twice, but they are from different tables.

Is there a way to fix this?

Thanks!

Answer 1

At the expense of a second reference to each table, you can try just selecting the primary keys in the subselect and then re-retrieving the data in the outer select:

SELECT s.*, m.*, a.*, AA.Rank 
FROM 
    (SELECT 
         s.Stats_id, m.Admin_X_id, a.Mod_Admin_id, 
         ROW_NUMBER() OVER (PARTITION BY s.aaa_id ORDER BY s.Stats_ID DESC) AS Rank
     FROM
         dev.dbo.Stats s
     INNER JOIN
         dev.dbo.Admin_X m ON s.Stats_id = m.Stats_id
     INNER JOIN
         dev.dbo.Mod_Admin a ON s.aaa_id = a.aaa_id
     WHERE
         m.early_reg_FLAG = 1 OR a.early_reg = 'Y'
    ) AA 
INNER JOIN
    dev.dbo.Stats s ON AA.Stats_id = s.Stats_id
INNER JOIN
    dev.dbo.Admin_X m ON AA.Admin_X_id = m.Admin_X_id
INNER JOIN
    dev.dbo.Mod_Admin a ON AA.Mod_Admin_id = a.Mod_Admin_id
WHERE
    AA.Rank = 1

Here, I've used Stats_id, Admin_X_id, and Mod_Admin_id as placeholders for the primary keys of the respective tables.

If either of the inner joins are many-to-1 (and not many-to-0/1), you may be able to eliminate that table reference from the inner subquery. (The join to Mod_Admin might qualify.)