January 26, 2012 at 4:45 pm
I have the following query
SELECT Distinct
SBBACLOAN
,[SBNAME1]
,[SBBRSSN]
,[SBNAME2]
,[SBCOBRSSN]
,APPRVLDTE
FROM [WarehouseStaging].[dbo].[AS400_ALSSLBACOUTP0] (nolock)
-- Where SENTOT > 0.0
WHERE APPRVLDTE is not null and APPRVLDTE <> '0001-01-01'
Which gives the following recordset:
68821013352599JAPHET AFAM15458402802012-01-24
68821013352599JAPHET AFAM154584028VERONICA N AFAM02011-12-28
How can i adjustr this so that it returns the record with the latest APPRVLDTE?
January 26, 2012 at 4:53 pm
ORDER BY
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 26, 2012 at 4:54 pm
won't that still return both records?
January 26, 2012 at 4:56 pm
SELECT TOP 1 WITH TIES
SBBACLOAN
,[SBNAME1]
,[SBBRSSN]
,[SBNAME2]
,[SBCOBRSSN]
,APPRVLDTE
FROM [WarehouseStaging].[dbo].[AS400_ALSSLBACOUTP0] (nolock)
WHERE APPRVLDTE is not null and APPRVLDTE <> '0001-01-01'
ORDER BY row_number() over(partition by
SBBACLOAN
,[SBNAME1]
,[SBBRSSN]
order by APPRVLDTE desc)
January 26, 2012 at 5:06 pm
wouldn't a simple group by accomplish the goal?
SELECT
SBBACLOAN
,[SBNAME1]
,[SBBRSSN]
,[SBNAME2]
,[SBCOBRSSN]
,APPRVLDTE=max(APPRVLDTE)
FROM [WarehouseStaging].[dbo].[AS400_ALSSLBACOUTP0]
WHERE APPRVLDTE is not null and APPRVLDTE <> '0001-01-01'
group by
SBBACLOAN
,[SBNAME1]
,[SBBRSSN]
,[SBNAME2]
,[SBCOBRSSN]
Maybe I misread. Please correct me. 😀
January 26, 2012 at 5:14 pm
MidBar has it, just need to add DESC to the 'order by APPRVLDTE'.
edit: now done.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply