As mentioned in the comments, your original problem is the unnecessary semicolon ( ; ).
By building the query the way you did, you are introducing SQL Injection Vulnerability. (obligatory XKCD reference). To prevent SQL Injections, use BIND variables.
Most RDBMS allow you to use ? as a place holder. Oracle supports this and Named place ...
You can achieve that by using this select:
from Employment_Form ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;
Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong ...
This answer is one more way to achieve the objective however answer provided by Glorfindel is correct and efficient.
Its always great to have DDL and some minimum reproducible code for one to answer. Below is the DDL of underlying table:
CREATE TABLE [dbo].[Test_SE](
[S NO] [smallint] NULL,
[NAME] [nchar](20) NULL,
[LAST NAME] [nchar](10) NULL,
You'll need the RANK function (here is documentation, assuming you're using Oracle). You basically group the records (normally you'd do that with GROUP BY, here it's the PARTITION BY clause) and rank/order them within that group. Then, you select the records with rank 1:
SELECT s.no FROM (
SELECT s.no, RANK() OVER (PARTITION BY name, last_name