SQL Pull Latest Distinct Records back based upon a column value

user_1510267 0
Viewed 21

I have data that where most of the columns have unique data. There are only three columns from the table that I am interested in and two of them have unique data.

Example data:

Ins_Cd | Encounter | Date
-------------------------------
A00    | 12345678  | 01-01-2001
A00    | 98765432  | 02-01-2001

From the above I want to return the second record

Ins_Cd | Encounter | Date
-------------------------------
A00    | 98765432  | 02-01-2001

I wrote the following code, which I think can be improved upon. It runs fairly quickly ~ 9 seconds whith just under 2 million records in the view.

SELECT Pyr1_Co_Plan_Cd
, PtNo_Num
, Dsch_Date
, [rn] = ROW_NUMBER() over(
    partition by pyr1_co_plan_cd 
    order by dsch_date desc
    )

into #temp

FROM schema.my_view

where Med_Rec_No is not null
and Dsch_Date is not null
and LEFT(PtNo_Num, 1) != '2'
and LEFT(ptno_num, 4) != '1999'
and LEFT(ptno_num, 1) != '9'

order by Pyr1_Co_Plan_Cd
, Dsch_Date desc
;

select a.Pyr1_Co_Plan_Cd
, a.PtNo_Num
, a.Dsch_Date

from #temp as a

where a.rn = 1

order by a.Pyr1_Co_Plan_Cd
;

drop table #temp
;

The above does give me what I want. How can I write this a bit more efficiently? Or should I be posting this on codereview

sql-server-2014 tsql sql-server
1 Answers

This should prbably go on codereview but since you are here...

Your code seems to have columns that isn't in your data... specifically your partitioned column. Maybe this is the correct column to partition on, but just seems odd.

One way to speed this up and to not use a temp table. This will speed things up because the INSERT operation doesn't have to be done. If you look at your actual execution plan, I bet the insert is a large % of the total query cost. Instead, use a derived table or CTE.

select
       Pyr1_Co_Plan_Cd
     , PtNo_Num
     , Dsch_Date
from
    (SELECT 
       Pyr1_Co_Plan_Cd
     , PtNo_Num
     , Dsch_Date
     , [rn] = ROW_NUMBER() over(partition by pyr1_co_plan_cd order by dsch_date desc)
    FROM 
       schema.my_view
    where 
       Med_Rec_No is not null
       and Dsch_Date is not null
       and LEFT(PtNo_Num, 1) != '2'
       and LEFT(ptno_num, 4) != '1999'
       and LEFT(ptno_num, 1) != '9') x
where RN = 1
order by Pyr1_Co_Plan_Cd

If you are set on using a TEMP TABLE then you can also speed things up by removing the order by Pyr1_Co_Plan_Cd, Dsch_Date desc on the SELECT INTO #temp portion. This ordering is unnecessary and doesn't benefit you at all especially because you are ordering your results in the final select, and are using a window function to calculate your RN which has an order by.

user_61678550
Related