November 3, 2003 at 7:28 am
How can I pull the absolute max StepNum? Here's my query and its result set.
SELECT max(StepNum) AS MaxStep,
CaseNum,
StepEndDt
FROM Loan_Step
WHERECaseNum = '200668'
GROUP BYCaseNum, StepEndDt
MaxStep CaseNum StepEndDt
6 200668 NULL
2 200668 01/01/1900
5 200668 11/03/2003
I only want the first row here in the result set.
Any thoughts as to what I'm doing wrong?
TIA
aurora01
Aurora
November 3, 2003 at 8:10 am
Use TOP 1 with your query as a derived table:
SELECT TOP 1 MaxStep, CaseNum, StepEndDt
FROM
(SELECT max(StepNum) AS MaxStep, CaseNum, StepEndDt
FROM Loan_Step
WHERE CaseNum = '200668'
GROUP BY CaseNum, StepEndDt) dt
ORDER BY MaxStep DESC
You might want to use "TOP 1 WITH TIES" depending on what you need from your result set...
--Jonathan
--Jonathan
November 3, 2003 at 8:13 am
That'll do it.
Thanks!
Aurora
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply