SQL Server 2008 SELECT -

i have table lots of columns. 1 of columns appointmentno, , column can either 1 or 2 (basically either first appointment or followup).

some of columns include

tblappoints:

clientid clientfirstname clientlastname clientaddress clientappointmentno 

i'm trying select clientid's table, however, don't want see clients clientappointmentno = 2. show clients have appointmentno = 1, no clients clientappointmentno = 2.

here 1 method, using aggregation:

select a.clientid tblappoints group a.clientid having max(clientappointmentno) = 1; 

if want see appointment details, 1 method uses window functions:

select a.* (select a.*,              max(clientappointmentno) on (partition a.clientid) maxcan       tblappoints     ) maxcan = 1;