user6576015 user6576015 - 4 months ago 13
SQL Question

SQL Server 2008 SELECT

I have a table with lots of columns. One of the columns is

AppointmentNo
, and this column can be either 1 or 2 (basically it is either a first appointment or followup).

Some of the columns include

tblAppoints:

ClientID
ClientFirstName
ClientLastName
ClientAddress
ClientAppointmentNo


I'm trying to select
clientID
's from this table, however, I don't want to see any clients where the
ClientAppointmentNo = 2
. So only show clients that have
AppointmentNo = 1
, no clients with
ClientAppointmentNo = 2
.

Answer

Here is one method, using aggregation:

select a.clientId
from tblAppoints a
group by a.clientId
having max(ClientAppointmentNo) = 1;

If you want to see the appointment details, then one method uses window functions:

select a.*
from (select a.*,
             max(ClientAppointmentNo) over (partition by a.clientId) as maxcan
      from tblAppoints a
    ) a
where maxcan = 1;