sql get the latest date and display the columns

Question

i want to display only 1 record with the latest date and display the other columns

lastname firstname datestarted   position               salary
lian     nez       2014-07-05    Cad Operator           4000
lian     nez       2015-06-02    Foreman                10000
lian     nez       2013-04-05    Manpower controller    13000

what i want is only this will be display because this is the latest record

lastname firstname datestarted   position               salary
lian     nez       2015-06-02    Foreman            10000

The query is

SELECT s.lastname,s.firstname,LatestDate,s1.position,s1.salary
FROM constructiondb.dbo.employeetb s
JOIN
(
    SELECT IDNo,position,salary,projectid, MAX(datestarted) AS LatestDate
    FROM constructiondb.dbo.eassignmenttb 
    GROUP BY IDNo,position,salary,projectid
) s1 ON s.IDNo = s1.IDNo
INNER JOIN constructiondb.dbo.ProjectTB s2 on s2.ProjectNo=s1.projectid
where lastname='lian'and firstname='nez'

Possible duplicate of [Selecting a Record With MAX Value](https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value) — EzLo, Jan 30 '19 at 10:45

score 1 · Answer 1 · answered Jan 30 '19 at 10:47

You can use RANK() :

SELECT TOP (1) WITH TIES s.lastname, s.firstname, s1.datestarted, s1.position, s1.salary
FROM constructiondb.dbo.employeetb s INNER JOIN
     constructiondb.dbo.eassignmenttb s1
     ON s1.IDNo = s.IDNo AND s1.projectid = s.projectid
ORDER BY RANK() OVER (PARTITION BY s.IDNo, s.projectid ORDER BY s1.datestarted DESC);

score 0 · Accepted Answer · answered Jan 30 '19 at 11:22

This will help you:

SELECT TOP 1 s.lastname, s.firstname, s1.datestarted, s1.position, s1.salary
FROM constructiondb.dbo.employeetb s 
INNER JOIN constructiondb.dbo.eassignmenttb s1 ON s1.IDNo = s.IDNo AND s1.projectid = s.projectid
ORDER BY s1.datestarted DESC;

sql get the latest date and display the columns

2 Answers2