select the latest result based on DateTime field

Question

I have a simple table with only 4 fields.

CREATE TABLE Assessment (
  id INTEGER IDENTITY(1,1) PRIMARY KEY,
  personId INTEGER NOT NULL,
  dateTaken DATETIME,
  outcomeLevel VARCHAR(2)
)

INSERT INTO Assessment (personId, dateTaken, outcomeLevel)
VALUES (1, '2014-04-01', 'L1')

INSERT INTO Assessment (personId, dateTaken, outcomeLevel)
VALUES (1, '2014-04-05', 'L2')

INSERT INTO Assessment (personId, dateTaken, outcomeLevel)
VALUES (2, '2014-04-03', 'E3')

INSERT INTO Assessment (personId, dateTaken, outcomeLevel)
VALUES (2, '2014-04-07', 'L1')

I am trying to select for each "personId" their latest assessment result based on the dateTaken.

So my desired output for the following data would be.

[personId, outcomeLevel]
[1, L2]
[2, L1]

Thanks, Danny

score 2 · Accepted Answer · answered Apr 10 '14 at 10:15

2

Try this:

;with cte as
(select personId pid, max(dateTaken) maxdate
 from assessment
 group by personId)

select personId, outcomeLevel
from assessment a
inner join cte c on a.personId = c.pid
where c.maxdate = a.dateTaken
order by a.personId

answered Apr 10 '14 at 10:15

shree.pat18

21,449
3
43
63

1

Can I just ask what the ; at the beginning is for? Just to make sure it terminates anything before? – Danny Cullen Apr 10 '14 at 10:33
1

Basically to demarcate which `WITH` it belongs to in a script. You may refer this for a great explanation: http://stackoverflow.com/questions/6938060/sql-server-common-table-expression-why-semicolon. Termination too, since a semicolon is used for that purpose. – shree.pat18 Apr 10 '14 at 10:35
Can you not use this within a LEFT JOIN of another query? Incorrect syntax near the keyword 'WITH'. – Danny Cullen Apr 10 '14 at 10:46
Depends on how you have written it. The CTE scope is restricted to the query immediately after it is defined. – shree.pat18 Apr 10 '14 at 10:48
I just attempted to wrap a LEFT JOIN ( your answer ) ASS ON ASS.personId = P.personId – Danny Cullen Apr 10 '14 at 10:52
Could you please edit and add to your question? Will be better that way. – shree.pat18 Apr 10 '14 at 10:54

Pred · Answer 2 · 2014-09-22T17:11:58.853

Here is a possible solution using common table expression:

WITH cte AS (
SELECT
    ROW_NUMBER() OVER (PARTITION BY personId ORDER BY dateTaken DESC) AS rn
    , personId
    , outcomeLevel
FROM
    [dbo].[Assessment]
)
SELECT
    personId
    , outcomeLevel
FROM
    cte
WHERE
    rn = 1

About CTEs

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. From MSDN: Using Common Table Expressions

score 1 · Answer 3 · answered Apr 10 '14 at 10:25

1

;with Cte as (Select personId,outcomeLevel, C= ROW_NUMBER()
            over(PARTITION By personId Order By dateTaken desc)
            From #Assessment
            )

Select * from cte where C=1

Sample here

answered Apr 10 '14 at 10:25

huMpty duMpty

14,346
14
60
99

score 1 · Answer 4 · answered Apr 10 '14 at 10:59

SELECT asst.personId,
       asst.outcomeLevel 
FROM dbo.Assessment asst
WHERE  asst.dateTaken=(SELECT MAX(ast.dateTaken) 
                       FROM assessment ast
                       WHERE asst.personid=ast.personId)
ORDER BY asst.personId

Result will be like this

personId outcomeLevel 1 L2 2 L1

score 0 · Answer 5 · answered Apr 10 '14 at 10:18

try this:

SELECT a.personId, a.outcomeLevel
FROM Assessment a
INNER JOIN 
  (
    SELECT max(dateTaken) as datetaken1, personId 
    FROM Assessment
    GROUP BY personId ) b 
ON a.dateTaken = b.datetaken1

demo: http://sqlfiddle.com/#!3/06d7d/9

Idea is to first derive a table with the max dates per person and then join that with the original table on the date field so you can get the outcome level for this maxed date...

score 0 · Answer 6 · answered Apr 10 '14 at 10:30

This should work perfectly without cte :

SELECT [Table4].[personId], [Table4].[outcomeLevel]
FROM (
    SELECT [Table1].[personId]
    FROM [Assessment] AS [Table1]
    GROUP BY [Table1].[personId]
    ) AS [Table2]
CROSS APPLY (
    SELECT TOP (1) [Table3].[personId], [Table3].[outcomeLevel], [Table3].[dateTaken]
    FROM [Assessment] AS [Table3]
    WHERE [Table2].[personId] = [Table3].[personId]
    ORDER BY [Table3].[dateTaken] DESC
    ) AS [Table4]
ORDER BY [Table4].[dateTaken] DESC

select the latest result based on DateTime field

6 Answers6