Selecting Row with Maximum?

[Database Administrator]

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?

[Database Administrator]

Something like:
SELECT EmpName, EmpBadge
FROM Employees
WHERE (EmpName=@EmpName1) AND id = (select Max(ID) from Employees)


"jp2msft" wrote in message
news836DEE9-807C-4E27-B42B-D587606C1F00@microsoft.com...
> 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?

[Database Administrator]

Hello.
A where clause in the SubSelec?
Like this:

> SELECT EmpName, EmpBadge
> FROM Employees
> WHERE (EmpName=@EmpName1) AND id = (select Max(ID) 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.



"Joe H" wrote:

> Something like:
> SELECT EmpName, EmpBadge
> FROM Employees
> WHERE (EmpName=@EmpName1) AND id = (select Max(ID) from Employees)
>
>
> "jp2msft" wrote in message
> news836DEE9-807C-4E27-B42B-D587606C1F00@microsoft.com...
> > 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?
>
>
>