Reputation: 35
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
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
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
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