Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

MS SQL 2005 Top 1 per account on history table

Need a sql query that will pull one record per AccNum based on the latest DateRec.
So the table lis like this

SO(ID), AccNum, Status, DateRec
1111,29,Closed,9/1/2011,120
1110,29,Closed,8/30/2011,100
1109.31,Closed,8/30/2011,100
1108,32,Closed,8/30/2011,50
1107,32,Closed,8/29/2011,50
1105,32,Closed,8/28/2011,50

Again, What I want to end up with is a just one record per Accnum, based on the latest DateRec

For this example I would end up with 3 rows
SO(ID), AccNum, Status, DateRec, Amount
1111,29,Closed,9/1/2011,120
1109.31,Closed,8/30/2011,100
1108,32,Closed,8/30/2011,50
0
matrixnetworks
Asked:
matrixnetworks
  • 3
  • 2
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
With Acct_Detail as
(
select
            SO
      ,      AccNum
      ,      Status
      ,      DateRec
      ,      row_number() over (Partition By AccNUm Order by DateRec desc) as Instance_Counter
)

select      *
from      Acct_Detail
where Instance_Counter = 1
0
 
Christopher GordonSenior Developer AnalystCommented:
Note:  The "row_number() over (... line will assign a unique number starting with 1 based on each record that has the same AccNum.  It is ordered by DateRec in descending order so each "Instance_Counter" with a "1", represents the most recent AccNum by date.
0
 
matrixnetworksAuthor Commented:
Where do I put the table that I am pulling the Acct_Details from?
Here is the real query info, but it's throwing an error,

WITH Acct_Details AS (SELECT     SONumber, AccountNumber, Status, DateReceived, row_number() over (Partition by AccountNumber Order by DateReceieved DESC) as Instance_Counter)
                                                 FROM          dbo.tblServiceOrders)
    SELECT    *
     FROM         Acct_Details
where Instance_Counter = 1
0
 
Christopher GordonSenior Developer AnalystCommented:
Sorry about that.  Typo.

WITH Acct_Details AS
(SELECT    
            SONumber
      ,      AccountNumber
      ,      Status
      ,      DateReceived
      ,      row_number() over (Partition by AccountNumber Order by DateReceieved DESC) as Instance_Counter

      FROM          dbo.tblServiceOrders
)

SELECT    *
FROM         Acct_Details
where Instance_Counter = 1
0
 
matrixnetworksAuthor Commented:
Thank you very much for your help!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now