HI ,

I am trying to have one record that gives some fields pertaining to the last record by Cl_ser_id having the Max  reg_strtdt and join the Max  reg_strtdt  -1.  I am using a UNION to get all these in the same row.

I am adding also the SQL code from Table U  for better understanding.

My code below gives errors and  I appreciate if one can help  with my issue.

With thanks .

Michelle

SELECT * FROM
(

SELECT a.*, b.reg_strtdt , b.RrankF, toint(max([RrankF])-1 ) AS MaxRank_1
from
    (SELECT cl_ser_id,
    cl_idno,
    reg_ser_no,
    reg_strtdt AS MaxStrtdt,
    toint(max([RrankF]) ) AS MaxRank
    
    FROM [U] r
    GROUP BY cl_ser_id,  r.cl_idno, reg_ser_no, reg_strtdt ) a

UNION

(    SELECT cl_idno,
    reg_ser_no,
    reg_strtdt AS MaxStrtdt,
    toint(max([RrankF])-1 ) AS MaxRank_1
    FROM
    [Table U] r1
    where toint(RrankF)= toint(max([RrankF])-1 )
    GROUP BY cl_ser_id,  r.cl_idno, reg_ser_no, reg_strtdt ) b
  )x

---------------------------------------------------------------------------

Table U

SELECT *,  RANKASC(h.cl_ser_id, h.reg_strtdt) AS RrankF
FROM [table H)  h
WHERE h.reg_part IN ('1','2', '5')
order BY h.cl_ser_id, h.reg_strtdt