CODEWITHSUNDEEP
sqlsql-serverselect
user6576015
user6576015

Reputation: 45

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.

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 2

Related Questions