get latest records from the dataset

Question

 

Tsseries     Enc      Policyno      ordernum
1             1        abc             1
1             1        abc1            2
1             2        abc             1
1             2        abc1            2
1             3        abc             1
1             3        abc1            2
2             4        abc             1
2             4        abc1            2
2             4        abc2            3
4             5        cad             1
4             5        cad2            2
10            51       cad             1
10            51       cad2             2

There is a table something like this.

 

I want the result to be

Since the policy numbers are same.. that means its for the same person and the ordernum decides which primaryu,seconday,or teitairy is used..

so for this.. i need the latest for all policy number..

the result should be

2        4    abc       1
2        4    abc1      2
10       51   cad       1
10       51   cad2      2      

so for that person for the most recent Tsseries get the most latest enc and its info.

Answer 1

try something like

SELECT DISTINCT POLICYNO, MAX(ORDERNO) AS ORDERNO, MAX(TsSERIES) AS TsSeries, MAX(ENC) AS ENC

FROM TABLE

GROUP BY POLICYNO

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 2

It is not very clear, so I will have to guess a bit:

; WITH CTE AS (
   SELECT Tsseries, Enc, Policyno, Ordernum,
          rowno = row_number(PARTITION BY Policyno
                             ORDER BY Tsseries DESC, ordernum DESC)
   FROM   tbl
)
SELECT Tsseries, Enc, Policyno, Ordernum
FROM    CTE
WHERE   rowno = 1

The sample data includes a policy abc2 that does not appear in the desired result. Why is not included?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Try:

;with cte as (select *, row_number() over (partition by PolicyNo order by Tsseries DESC, Enc DESC) as Rn from PolciesInfo)

select * from cte where Rn = 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog