The error message makes perfect sense and is quite clear. What are you taking a maximum of? Where is the group by, or over clause?. See you can find a book on SQL and read how the clauses are executed; in particular, pay attention to the scoping rules. This is one of the tricky parts of the language. My guess is that you might want to do it with something like this:

WITH Archive_Max (staging_eligibility_id_max)

AS

(SELECT MAX(staging_eligibility_id)

FROM Staging_Eligibility_Archive)

WITH Staging_Max (staging_eligibility_id_max)

AS

(SELECT MAX(staging_eligibility_id)

FROM Staging_Eligibility_Somethings)

SELECT S.* -- bad coding!

FROM Archive_Max AS A,

Staging_Max AS S

WHERE A.staging_eligibility_id_max <> S.staging_eligibility_id_max.

But frankly, I think you need to throw for this out and start over. We do not use "select *" in production code; it is unpredictable, and very expensive to execute. Eligibility is not an entity or relationship! It is an attribute, and we do not put attributes in their own tables. We also do not have a identifiers for values.

Since you did not bother to post DDL (please, please read the forum rules), we cannot be much help to you. This invalid table looks like it is getting worse. You seem to have a design flaw called "attribute , which means that you views an attribute to set up multiple tables when you should have only had one. The example I like to use it. I am teaching this is having a schema with "male_personnel" and "female_personnel" which obviously should have been merely "personnel"; it was split on the sex code. Yours seems to be split between staging and archive, which are status attributes.