SQL query get distinct records

Question

Hi,

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?

Answer 1

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

Answer 2

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.

Answer 3

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