Using UNION to get the Max Rank() and the Max Rank() -1
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
Please sign in to leave a comment.
Comments
0 comments