title: SQL Server Group by with WHEN clause
body: <p>Error in group by clause ::
  I want make records unique in MS sql.here i have used 2-3 tables join and when clause.
  it is more difficult to make it distinct by using group by clause it gives error like..</p>
  
  <pre><code>Error :::: Incorrect syntax near the keyword 'GROUP'
  </code></pre>
  
  <hr>
  
  <pre><code>select * from(
      SELECT TOP 5 m.pk_member_id,m.first_name+' '+m.middle_name+' '+m.last_name as "NAME", 
      m.gender AS "GENDER",
      datediff(Year,m.date_of_birth, getdate()) AS "AGE", 
      m.home_email AS "EMAIL", 
      m.business_phone AS "PHONE1", 
      m.cell_phone AS "PHONE2", 
      m.address+','+m.city+','+m.state+','+m.zip_code AS "ADDRESS", 
      paidamt = 
              CASE WHEN (moi.credit_card_amt!=null or moi.credit_card_amt &gt; 0) THEN moi.credit_card_amt 
                   WHEN (moi.cheque_amt!=null or moi.cheque_amt &gt; 0) THEN moi.cheque_amt 
                   WHEN (moi.debit_card_amt!=null or moi.debit_card_amt &gt; 0) THEN moi.debit_card_amt 
                   WHEN (moi.cash_amt!=null or moi.cash_amt &gt; 0) THEN moi.cash_amt 
              END, 
      mode = 
              CASE WHEN (moi.credit_card_amt!=null or moi.credit_card_amt &gt; 0) THEN 'CREDIT CARD' 
                     WHEN (moi.cheque_amt!=null or moi.cheque_amt &gt; 0) THEN 'CHEQUE' 
                     WHEN (moi.debit_card_amt!=null or moi.debit_card_amt &gt; 0) THEN 'DEBIT CARD' 
                     WHEN (moi.cash_amt!=null or moi.cash_amt &gt; 0) THEN 'CASH' 
              END, 
      m.registration_date as "JOINING DATE", 
      m.membership_expiry_date as "EXPIRY DATE", 
          services = 
                  CASE WHEN m.pk_member_id=em.fk_member_id THEN 'EVENT' 
                           WHEN m.pk_member_id=dm.fk_member_id THEN 'DINING' 
                           WHEN m.pk_member_id=jm.fk_member_id THEN 'JF2' 
                  END 
          FROM 
          Member m, 
          Member_Official_Info moi, 
          Event_Members em, 
          Dining_Members dm, 
          JF2_Members jm 
          WHERE 
          m.pk_member_id = moi.fk_member_id and 
          (m.pk_member_id = em.fk_member_id or m.pk_member_id = dm.fk_member_id or m.pk_member_id = jm.fk_member_id) and 
          YEAR(m.membership_expiry_date)=2008
          )
          GROUP BY 
          m.pk_member_id,m.first_name,m.middle_name, m.last_name,m.business_phone,m.cell_phone,m.address,m.city,\
          m.state,m.zip_code,m.gender,m.date_of_birth,m.home_email,m.registration_date, 
          m.membership_expiry_date,services,paidamt,mode;
  </code></pre>