Db2 select query with group by clause throws error?

Question

I want to get Distinct values of SERIAL_NUM and EMP_NAME but the below query throws an error.

SELECT SERIAL_NUM,EMP_NAME,NOTESID FROM DB2INST1.EMP_HC 
GROUP BY 
SERIAL_NUM,EMP_NAME,NOTESID 
HAVING   
FUNC_VP ='Bob '  AND 
VICE_PRES ='Bob'  AND 
DIRECTOR ='Michael'  AND 
THIRD_LINE ='Linda ' 

error

DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=FUNC_VP, DRIVER=3.63.123

Solution

If you want "distinct values of SERIAL_NUM and EMP_NAME" you need to tell the database which notesid to pick if there are multiple combinations of serial_num and emp_name. If you don't care which notesid you get, you can e.g. use min()

SELECT serial_num,
       emp_name,
       min(notesid) as notesid
FROM db2inst1.emp_hc 
WHERE func_vp ='Bob '  
  AND vice_pres ='Bob'  
  AND director ='Michael'  
  AND third_line ='Linda'  
GROUP BY serial_num,emp_name

Comments

[user3398326]

however when i use min(notesid) i cannot get the value since resultSet.getString("NOTESID"); throws an error when i try to iterate through the result set ?

[a_horse_with_no_name]

@user3398326: did you use the column alias the way I did? (as notesid)