General information
Category:
Coding, syntax & commands
>>>The table has three fields which I am concerned with.
>>>ProjectStatus
>>>TalentId
>>>Date
>>>
>>>There can be multiple records of the same TalentId. I want to in one SQL statement get the most recent ProjectStatus by looking at the date and be able to filter the ProjectStatus based on user input. I know this can be done in two steps. I want to know if there is a clean way of doing that in one.
>* lcList is a comma delimited list of use selections.
>
>SELECT TOP 1 ItemDate, TalentId, cItemType ;
> FROM vItemTal ;
> WHERE INLIST( vItemTal.cItemType, &lcList )
> ORDER BY 1 DESC ;
> GROUP BY 2
> INTO CURSOR currParent
>
The question I have is if the most recent record is not in the list, do you want to (1) exclude the Talent from the query, or (2) obtain the most recent for that Talent whose status is in the list? Here are the answers, depending:
(1) SELECT MAX( ItemDate), TalentId, cItemType ;
FROM vItemTal ;
GROUP BY 2
HAVING cItemType IN ( &lcList )
(2) SELECT MAX( ItemDate), TalentId, cItemType ;
FROM vItemTal ;
WHERE cItemType IN ( &lcList ) ;
GROUP BY 2
Note that you cannot use TOP in cases where you also need to GROUP.
-- Randy
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only