Selecting Row with Maximum?

[jp2msft]

I've got this simple query:

SELECT EmpName, EmpBadge
FROM Employees
WHERE EmpName=@EmpName1

Occasionally, there are duplicates (an employee is terminated, then hired
again at a later date). Because of this, we have added an ID field that is
automatically incremented whenever a new employee is added.

To prevent duplicates, I need to modify the query above to something like
this code below that does NOT work as it is written:

SELECT EmpName, EmpBadge
FROM Employees
WHERE (EmpName=@EmpName1) AND (Max(ID))

Obviously, the Max(ID) parameter is causing the failure.

I do not want the ID returned as part of the query, or I will need to write
another query to filter the ID out of the results.

So, how exactly should this query be written?

[Joe H]

Something like:
SELECT EmpName, EmpBadge
FROM Employees

Post by jp2msft
SELECT EmpName, EmpBadge
FROM Employees
Occasionally, there are duplicates (an employee is terminated, then hired
again at a later date). Because of this, we have added an ID field that is
automatically incremented whenever a new employee is added.
To prevent duplicates, I need to modify the query above to something like
SELECT EmpName, EmpBadge
FROM Employees
Obviously, the Max(ID) parameter is causing the failure.
I do not want the ID returned as part of the query, or I will need to write
another query to filter the ID out of the results.
So, how exactly should this query be written?

[Ricardo Junquera]

Hello.
A where clause in the SubSelec?

Post by jp2msft
SELECT EmpName, EmpBadge
FROM Employees

WHERE (EmpName=@EmpName1)
)
--
Ricardo Junquera
Consultor Business Intelligence

BG&S Online Consultores
Ganadora del Premio Microsoft Business Awards 2008.
Partner de Soluciones : Satisfacción de Cliente.

Post by jp2msft
SELECT EmpName, EmpBadge
FROM Employees

Post by jp2msft
SELECT EmpName, EmpBadge
FROM Employees
Occasionally, there are duplicates (an employee is terminated, then hired
again at a later date). Because of this, we have added an ID field that is
automatically incremented whenever a new employee is added.
To prevent duplicates, I need to modify the query above to something like
SELECT EmpName, EmpBadge
FROM Employees
Obviously, the Max(ID) parameter is causing the failure.
I do not want the ID returned as part of the query, or I will need to write
another query to filter the ID out of the results.
So, how exactly should this query be written?

[unknown]

If you already have the field MaxId in your table then make sure its an auto increment one.Using your query pick the Maximum from MaxId.