title: DB2 and SQL-How to return max value in a particular field so that only one record shows up per row; pulling data from multiple tables
body: <p>All,
  My data set below pulls WONO, EPLSNM, and SQNO1 from three separate tables.  What I need is a db2 sql query that will return the Employee Name (EPLSNM) per unique WONO with the max SQNO1.</p>
  
  <pre><code>WONO    EPLSNM               SQNO1
  CA04269 WILLIAMS, BILLY W   12
  CA04269 WILLIAMS, BILLY W   12
  CA04269 WILLIAMS, BILLY W   12
  CA04269 WILLIAMS, BILLY W   12
  CA04270 FAIRCLOTH, MARK E   22
  CA04270 FAIRCLOTH, MARK E   22
  CA04270 FAIRCLOTH, MARK E   22
  CA04270 FAIRCLOTH, MARK E   22
  CA04270 FAIRCLOTH, MARK E   23
  CA04270 FAIRCLOTH, MARK E   23
  CA04270 FAIRCLOTH, MARK E   23
  CA04270 FAIRCLOTH, MARK E   23
  </code></pre>
  
  <p>I would like to see the following if possible:</p>
  
  <pre><code>WONO    EPLSNM               SQNO1
  CA04269 WILLIAMS, BILLY W   12
  CA04270 FAIRCLOTH, MARK E       23
  </code></pre>
  
  <p>I only need the 2 rows of data versus the 12 I got.  </p>
  
  <p>Thank you so much.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>    SELECT DISTINCT WOHDR.wono,EMP.eplsnm,max(WOLBR.sqno1) AS SQNO,WOLBR.cgcd,CASE WHEN WOHDR.opndt8 IN (' ') THEN DATE('1900-01-01') WHEN WOHDR.opndt8 IS NULL THEN DATE('1900-01-01') ELSE Date(Cast(SUBSTRING(WOHDR.opndt8,1,4) as char(4)) ||'-'|| Cast(SUBSTRING(WOHDR.opndt8,5,2) as char(2)) ||'-'|| Cast(SUBSTRING(WOHDR.opndt8,7,2) as char(2))) END AS OpenDate,CASE WHEN WOHDR.prmdt8 IN (' ') THEN DATE('1900-01-01') WHEN WOHDR.prmdt8 IS NULL THEN DATE('1900-01-01') ELSE Date(Cast(SUBSTRING(WOHDR.prmdt8,1,4) as char(4)) ||'-'|| Cast(SUBSTRING(WOHDR.prmdt8,5,2) as char(2)) ||'-'|| Cast(SUBSTRING(WOHDR.prmdt8,7,2) as char(2))) END AS PromisedDate,WOHDR.divi AS Division, WOHDR.cunm AS CustName, WOHDR.eqmfmd AS Model,WOHDR.stno AS StoreNo,RPT.ds5 AS StoreName,WOHDR.acti
  
  FROM libd09.wophdrs0 WOHDR 
  JOIN libd09.wopsegs0 WOSEG ON (WOHDR.wono = WOSEG.wono)
  JOIN libd09.fnlrlvl0 RPT ON (WOHDR.stno = RPT.st4 AND WOHDR.stno = WOSEG.stn1)
  LEFT JOIN libd09.cipname0 NME ON (NME.cuno = WOHDR.cuno)
  LEFT JOIN libd09.woplabr0 WOLBR ON (WOHDR.wono = WOLBR.wono)
  LEFT JOIN libd09.wopempf0 EMP ON (EMP.epidno = WOLBR.epidno AND WOHDR.stno = EMP.stn1)
  WHERE WOHDR.opndt8 BETWEEN 20130101 AND YEAR(CURRENT DATE) * 10000 + MONTH(CURRENT DATE) * 100 + DAY(CURRENT DATE) 
  AND WOHDR.divi = 'G'
  AND NME.cuno NOT LIKE ('I%')
  AND RPT.rplvl = ' ' 
  AND WOHDR.cunm NOT IN ('ACCRUED VACATION TIME','ACCRUED VACATION','SICK TIME','SICK PAY')
  AND WOHDR.acti IN ('O','C')
  -- AND DATE(Cast(SUBSTRING(WOLBR.docdt8,1,4) as char(4)) ||'-'|| Cast(SUBSTRING(WOLBR.docdt8,5,2) as char(2)) ||'-'|| Cast(SUBSTRING(WOLBR.docdt8,7,2) as char(2))) = DATE(RTRIM(CHAR(WOHDR.llbjd8)))
  
  GROUP BY
  WOHDR.wono,EMP.eplsnm,WOLBR.cgcd,WOHDR.opndt8,WOHDR.divi,WOHDR.cunm, WOHDR.eqmfmd,WOSEG.wosgno,WOHDR.stno,WOHDR.prmdt8,RPT.ds5,WOHDR.acti
  </code></pre>