SQL query get distinct records

I need help with a query to get distinct records from the table.

     开发者_运维技巧       SELECT distinct cID, firstname, lastname,
             typeId, email from tableA 

typeId and email has different values in the table. I know this one causing to return 2 records because these values are different.

Is there anyway I can get 1 record for each cID irrespective of typeId and email?

If you don't care about what typeId and email get selected with each cID, following is one way to do it.

SELECT  DISTINCT a.cID
        , a.firstname
        , a.lastname
        , b.typeId
        , b.email 
FROM    TableA a
        INNER JOIN (
          SELECT  cID, MIN(typeID), MIN(email)
          FROM    TableA 
          GROUP BY
                  cID
        ) b ON b.cID = a.cID

If any one value for typeId and email are acceptable, then

SELECT cID, firstname, lastname, 
  max(typeId), max(email)
 from tableA
 group by  cID, firstname, lastname, 

should do it.

Is this what you are after:?

SELECT distinct a.cID, a.firstname, a.lastname, (SELECT typeId from tableA WHERE cID = a.cID), (Select email from tableA WHERE cID = a.cID) from tableA AS a