Best way to select most recent record

Question

So the database I am using does not have a great way to select the most recent number by its unique ID. We have to narrow down to get the most recent record with a bunch of sub queries joining back to the original table. The original table is TBL_POL. Ex.

Policy_ID   Load_DATE   ENDORSEMENT#    SEQUENCE    EXTRACTDATE
25276       8/16/2015       0               1        8/15/2015      
25276       2/13/2016       1               2        2/12/2016
25276       9/24/2016       3               4        9/20/2016
25276       9/24/2016       3               4        9/20/2016
25276       9/24/2016       2               3        9/20/2016

so first we grab the max load date and join back to the original table and then grab the max endorsement # and then join back and grab the max sequence and then join back and get the max extract date to finally get back to our final record so it will be unique. Above is an example.

Is there an easier way to do this? Someone mentioned row_number() over(partition by), but I think that just returns the whatever row number you would like. I am for a quick way to grab the most record with all these above attributes in one swipe. Does anyone have a better idea to do this, because these queries take a little while to run.

Thanks

Answer 1

May be you should try Grouping SET

Throw another sample data. Also i am not sure about performance.

Give Feedback but result and performance both

SELECT *
FROM (
    SELECT Policy_ID
        ,max(Load_DATE) Load_DATE
        ,max(ENDORSEMENT#) ENDORSEMENT#
        ,max(SEQUENCE) SEQUENCE
        ,max(EXTRACTDATE) EXTRACTDATE
    FROM #TBL_POL t
    GROUP BY grouping SETS(Policy_ID, Load_DATE, ENDORSEMENT#, SEQUENCE, EXTRACTDATE)
    ) t4
WHERE Policy_ID IS NOT NULL

     drop table #TBL_POL

Answer 2

@Bryant,

First, @Backs saved this post for you. When I first looked at it I thought "Damn. If he doesn't care to spend any time making his request readable, why should I bother"? Further, if you're looking for a coded example, then it would be good to create some readily consumable test data to make it a whole lot easier for folks to help you. Also, as @Felix Pamittan suggested, you should also post what your expected return should be.

Here's one way to post readily consumable test data. I also added another Policy_ID so that I could demonstrate how to do this for a whole table instead of just one Policy_ID.

--===== If the test table doesn't already exist, drop it to make reruns in SSMS easier.
     -- This is NOT a part of the solution. We're just simulating the original table
     -- using a Temp Table.
     IF OBJECT_ID('tempdb..#TBL_POL','U') IS NOT NULL
   DROP TABLE #TBL_POL
;
--===== Create the test table (technically, a heap because no clustered index)
     -- Total SWAG on the data-types because you didn't provide those, either.
 CREATE TABLE #TBL_POL
        (
         Policy_ID      INT     NOT NULL
        ,Load_DATE      DATE    NOT NULL
        ,ENDORSEMENT#   TINYINT NOT NULL
        ,SEQUENCE       TINYINT NOT NULL
        ,EXTRACTDATE    DATE    NOT NULL
        )
;
--===== Populate the test table
 INSERT INTO #TBL_POL
        (Policy_ID,Load_DATE,ENDORSEMENT#,SEQUENCE,EXTRACTDATE)
 SELECT Policy_ID,Load_DATE,ENDORSEMENT#,SEQUENCE,EXTRACTDATE
   FROM (VALUES
        --===== Original values provided
         (25276,'8/16/2015',0,1,'8/15/2015')  
        ,(25276,'2/13/2016',1,2,'2/12/2016')
        ,(25276,'9/24/2016',3,4,'9/20/2016')
        ,(25276,'9/24/2016',3,4,'9/20/2016')
        ,(25276,'9/24/2016',2,3,'9/20/2016')
        --===== Additional values to demo multiple Policy_IDs with
        ,(12345,'8/16/2015',0,1,'8/15/2015')  
        ,(12345,'9/24/2016',1,5,'2/12/2016')
        ,(12345,'2/13/2016',1,2,'2/12/2016')
        ,(12345,'9/24/2016',3,4,'9/20/2016')
        ,(12345,'9/24/2016',3,4,'9/20/2016')
        ,(12345,'9/24/2016',2,3,'9/20/2016')

        ) v (Policy_ID,Load_DATE,ENDORSEMENT#,SEQUENCE,EXTRACTDATE)
;
--===== Show what's in the test table
 SELECT * 
   FROM #TBL_POL
;

If you are looking to resolve your question for more than one Policy_ID at a time, then the following will work.

--===== Use a partitioned windowing function to find the latest row
     -- for each Policy_ID, ignoring "dupes" in the process.
     -- This assumes that the "sequence" column is king of the hill.
   WITH cteEnumerate AS
        (
         SELECT *
                ,RN = ROW_NUMBER() OVER (PARTITION BY Policy_ID ORDER BY SEQUENCE DESC)
           FROM #TBL_POL
        )
 SELECT Policy_ID,Load_DATE,ENDORSEMENT#,SEQUENCE,EXTRACTDATE
   FROM cteEnumerate
  WHERE RN = 1
;

If you're only looking for one Policy_ID for this, the "TOP 1" method that @ZLK suggested will work but so will adding a WHERE clause to the above. Not sure which will work faster but the same indexes will help both. Here's the solution with a WHERE clause (which could be parameterized).

--===== Use a partitioned windowing function to find the latest row
     -- for each Policy_ID, ignoring "dupes" in the process.
     -- This assumes that the "sequence" column is king of the hill.
   WITH cteEnumerate AS
        (
         SELECT *
                ,RN = ROW_NUMBER() OVER (PARTITION BY Policy_ID ORDER BY SEQUENCE DESC)
           FROM #TBL_POL
          WHERE Policy_ID = 25276
        )
 SELECT Policy_ID,Load_DATE,ENDORSEMENT#,SEQUENCE,EXTRACTDATE
   FROM cteEnumerate
  WHERE RN = 1
;