How to pull the latest records added in a table?

Question

I want to pull the records associated with the latest VERSION_ID. FILE_EXTRACT_VERSION table looks like this:

 VERSION_ID VERSION_TAG  RELEASE_DATE   
    1           1.0.0    2019-10-15 11:49:02.283    
    2           2.0.0    2019-10-15 13:54:32.940

FILE_EXTRACT_VERSION_SPECS table looks like this:

 VERSION_ID   FILE     COLUMN_HEADER
    1       SCHOOLS    LOCAL_SCHOOL_KEY
    1       SCHOOLS    SCHOOL_YEAR
    1       SCHOOLS    DISTRICT_CODE
    2       STUDENT    SCHOOL_YEAR
    2       SCHOOLS    SCHOOL_MONTH
    2       SCHOOLS    CITY_CODE

I want to write a query to pull records with the latest VERSION_ID (latest can be segregated by the latest date they have been added)

Here is what I have tried but it gives an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Please advise the right query to do this:

SELECT [FILE], COLUMN_HEADER
FROM FILE_EXTRACT_VERSION_SPECS
WHERE [VERSION_ID] = (SELECT B.[FILE], B.COLUMN_HEADER, A.RELEASE_DATE
                      FROM FILE_EXTRACT_VERSION_SPECS B
                      LEFT JOIN FILE_EXTRACT_VERSION A ON A.version_id = B.version_id
                      GROUP BY [A].[RELEASE_DATE], B.[FILE], B.COLUMN_HEADER
                      HAVING A.RELEASE_DATE = MAX(A.RELEASE_DATE));

score 2 · Accepted Answer · answered Oct 15 '19 at 19:34

You can use a subquery to select the latest version_id, and filter the original table with the result:

select vs.*
from file_extract_version_specs vs
where version_id  = (
    select top 1 version_id from file_extract_version  order by release_date desc
)

score 1 · Answer 2 · answered Oct 15 '19 at 19:38

1

You can also do it with a join like this

select vs.*
from file_extract_version_specs vs
join (
  select top 1 version_id 
  from file_extract_version
  order by release_date  desc
) as tv on vs.version_id = tv.version_id

answered Oct 15 '19 at 19:38

Hogan

69,564
10
76
117

score 1 · Answer 3 · answered Oct 15 '19 at 19:44

I would use an OLAP function to achieve this:

SELECT fevs.*
FROM FILE_EXTRACT_VERSION_SPECS fevs
INNER JOIN (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY VERSION_ID ORDER BY RELEASE_DATE DESC) AS rn
    FROM FILE_EXTRACT_VERSION
) AS fev
    ON fevs.VERSION_ID = fev.VERSION_ID
    AND FILE_EXTRACT_VERSION.rn = 1

How to pull the latest records added in a table?

3 Answers3