Rabelani Netshifhire
Rabelani Netshifhire

Reputation: 35

Retrieve Most Recent Activity in SQL

Good Day

I have a table of Activities that occurred in different dates and can be retrieved with the query (T-SQL) bellow:

Select  Activities.activityid,
        Activities.ActivityTypeCode,
        Activities.ActivityDate,
        Activities.SequenceOrder,
        Activities.Place,
        Activities.TypeOfPlace
From Activities
Where Activities.ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')

Queston: How can I retrieve the most recent one? or the second most recent?

For your info: There is a certain field called sequenceOrder that tracks the sequence when the activities are being entered.

Please help.

Upvotes: 2

Views: 2810

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521579

Use ROW_NUMBER:

SELECT
    activityid,
    ActivityTypeCode,
    ActivityDate,
    SequenceOrder,
    Place,
    TypeOfPlace
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY SequenceOrder DESC) rn
    FROM Activities
    WHERE ActivityTypeCode IN ('LOST', 'FOUND', 'NOT SEEN')
) t
WHERE rn = 1;

The advantage of this approach is that if you want the second most recent event, we can use WHERE rn = 2.

Note that if there could be ties for values in the sequence order, then we might want to use a rank function instead of row number.

You may place the above query into a CTE (common table expression):

WITH cte AS (
    SELECT
        activityid,
        ActivityTypeCode,
        ActivityDate,
        SequenceOrder,
        Place,
        TypeOfPlace
    FROM
    (
        SELECT *,
            ROW_NUMBER() OVER (ORDER BY SequenceOrder DESC) rn
        FROM Activities
        WHERE ActivityTypeCode IN ('LOST', 'FOUND', 'NOT SEEN')
    ) t
    WHERE rn = 1
)

SELECT * FROM cte;

But note that wrapping a query in a subquery is not desirable from a performance point of view. Instead, just tailor my original query to fit your needs.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use GROUP BY clause with sub-query

SELECT * 
FROM Activities
WHERE sequenceOrder IN (
              SELECT MAX(SequenceOrder) 
              FROM Activities
              WHERE ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')
              GROUP BY ActivityTypeCode 
)

If, you want to get the only top most activity among one of (LOST, FOUND, NOT SEEN) then remove GROUP BY clause from sub-query.

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

select * from (
Select  Activities.activityid,
    Activities.ActivityTypeCode,
    Activities.ActivityDate,
    Activities.SequenceOrder,
    Activities.Place,
    Activities.TypeOfPlace,
    row_number() over (partition by Activities.ActivityTypeCode order by Activities.SequenceOrder) [rn]
From Activities
Where Activities.ActivityTypeCode In ('LOST', 'FOUND', 'NOT SEEN')
) a where rn = 1 --2 if you want second most recent

Upvotes: 0

Related Questions