# get latest records from the dataset

### 問題

•

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

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

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

2012年8月17日 下午 09:25

### 解答

• 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, esquel@sommarskog.se
2012年8月17日 下午 10:12
• 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

2012年8月18日 下午 08:00

### 所有回覆

• try something like

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

FROM TABLE

GROUP BY POLICYNO

• 已提議為解答 2012年8月17日 下午 09:43
• 已取消提議為解答 2012年8月18日 下午 07:57
2012年8月17日 下午 09:43
• 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, esquel@sommarskog.se
2012年8月17日 下午 10:12
• 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

2012年8月18日 下午 08:00