【Question】

Hello- Any help on writing a SQL query to retrieve one record for each employee from the following table? A standard SQL is preferred.

EmpNum Alternate Contact Relation PhType Phone 

============================================ 

123456 Rick Grimes SP Cell 9999999999 

123456 Rick Grimes SP Work 8888888888 

123457 Daryl Dixon FR Work 7777777777 

123457 Daryl Dixon FR Home 3333333333 

123458 Maggie Greene CH Cell 5555555555 

123458 Maggie Greene CH Home 6666666666 

Expected result:

EmpNum Alternate Contact Relation PhType Phone 

================================================= 

123456 Rick Grimes SP Cell 9999999999 

123457 Daryl Dixon FR Work 7777777777 

123458 Maggie Greene CH Home 6666666666 

【Answer】

According to SQL2003, you can use window functions to solve your problem. First you group data by EmpNum, and then get the first record from each group according to a certain rule. The rule can be sequence numbers of records in each group or the position of a certain field. To sort the records by Phone in ascending order and get the first record from each group, for example, we can use the following SQL query:

SELECT empnum, alternate, contact, relation, phtype, phone

FROM (

       SELECT a.*, row_number()over(PARTITION BY empnum ORDER BY phone ASC) rid 

       FROM t1 a)

WHERE rid= 1

Databases give different supports for window functions. Some even don’t give any support. According to SQL92, you need to use a JOIN to achieve the effect of order as window functions can. That is difficult. Try using SPL (Structured Process Language) to handle this. The language supports getting records by their sequence numbers in the group. Only one-liner is enough:

There are more complicated scenarios explained in SPL Simplified SQL Case Details: Calculate the first N rows of each group.

An SPL script can be integrated with a Java application via esProc JDBC, which is similar to calling a database result set. See How to Call an SPL Script in Java to learn more details.