Agfdhyk

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

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.