CODEWITHSUNDEEP
sqlsql-servermax
katy89
katy89

Reputation: 161

Select the Latest Record

I'm trying to aggregate 3 rows to one row (select the latest record). But I still get 3 records instead of 1. Any thoughts ? ** SQL Server**

select [TotalTechPremium] From Premiumfile
where [TimeDate Stamp] = (select Max([TimeDate Stamp]) 
                          From Premiumfile
                          where PolicyNo = '47-ZCA-000276-02'
                         );

Upvotes: 0

Views: 103

Answers (4)

TDP
TDP

Reputation: 1231

Here are two different ways of using ROW_NUMBER()

create table #t (id1 int, id2 int)

insert into #t
select 1,1 union
select 1,2 union
select 2,1 union
select 2,2

select * from #t

;WITH CTE AS(
    SELECT id1,
           ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY id1 DESC) AS RN
    FROM #t)
SELECT id1
FROM CTE
WHERE RN = 1;

select id1 from (
    SELECT id1,
           ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY id1 DESC) AS RN
    FROM #t
) z
where rn = 1

drop table #t

If you set Include Actual Executions Plans and execute the above you can see that the executions plans are identical.

Upvotes: 1

James
James

Reputation: 3015

If that query outputs 3 records, it means that there are 3 records in the table PremiumFile with a Timestamp equal to the maximum timestamp for that policy number. Maybe you are forgetting to add an additional where where you filter again by that Policy number. Or maybe you can replace your query by:

select top 1 TotalTechPremium
from Premiumfile
where PolicyNo='47-ZCA-000276-02'
order by Timestamp desc

Upvotes: -1

Thom A
Thom A

Reputation: 95554

Sample data and expected results are also so important when asking a T-SQL question.

As you haven't provided this, this is a guess, but a common way of getting the "latest" record is using a CTE and ROW_NUMBER. An example query would look like:

WITH CTE AS(
    SELECT [YourColumns]
           ROW_NUMBER() OVER (PARTITION BY [RelevantColumns] ORDER BY [TimestampColumn] DESC) AS RN
    FROM [YourTable])
SELECT [YourColumns]
FROM CTE
WHERE RN = 1;

If this doesn't help, have a look at http://www.sqlservercentral.com/articles/Best+Practices/61537/ on Forum Etiquette on how to post a T-SQL Question.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You have multiple records with the same timestamp.

This might help:

select pf.TotalTechPremium
From Premiumfile pf
where pf.PolicyNo = '47-ZCA-000276-02'
      pf.[TimeDate Stamp] = (select Max(pf2.[TimeDate Stamp]) 
                             From pf2.Premiumfile
                             where pf2.PolicyNo = pf.PolicyNo
                            );

But if you want one row, I would just do:

select top (1) pf.TotalTechPremium
From Premiumfile pf
where pf.PolicyNo = '47-ZCA-000276-02'
order by pf.[TimeDate Stamp] desc;

Upvotes: -1

Related Questions