[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Grouping within groups: SQL

I have the data in Exhibit 1 of the attachment which I get by running the query shown below. However, what I am looking to do is to get my data to look like Exhibit 2 of the attached so that I only retain the records with the later Mod_Date for every Loan_ID by setting row1=1. Can anyone please offer their input? Any help would be extremely appreciated. Many thanks in advance.
 
select  t.Loan_ID, t.Mod_Option, t.Mod_Date, t.Loan_UPB, t.row1,
            row_number() over(partition by Mod_Option) as row2
from(select  Loan_ID, Mod_Option, Mod_Date, Loan_UPB,
            row_number() over(partition by Loan_ID order by a.Mod_Date) as row1
           from Exhibit1) t
Example.docx
0
maroulator
Asked:
maroulator
  • 3
2 Solutions
 
PortletPaulCommented:
using copy/paste only, into a "code block" you get this:
Exhibit 1:
Loan_ID  Mod_Option    Mod_Date     Loan_UPB   row1  row2
1341               3                  10/2/2011       123,876       1          1
1341               3                  10/2/2011       123,876       2          2
1341               3                  10/2/2011       123,531       3          3
1341               3                  10/2/2011       123,531       4          4
1341               4                     9/2/2011      123,355       5          1
1341               4                     9/2/2011      123,355       6          2
3211               2                     3/4/2014      115,780       1          1
3211               2                     3/4/2014      115,780       2          2
3211               2                     3/4/2014      115,780       3          3
3211               1                     2/3/2014      115,520       4          1
3211               1                     2/3/2014      115,520       5          2 

Exhibit 2:
Loan_ID  Mod_Option    Mod_Date     Loan_UPB   row1  
1341               3                  10/2/2011       123,876       1          
1341               3                  10/2/2011       123,876       1          
1341               3                  10/2/2011       123,531       1          
1341               3                  10/2/2011       123,531       1          
1341               4                     9/2/2011      123,355       2          
1341               4                     9/2/2011      123,355       2          
3211               2                     3/4/2014      115,780       1          
3211               2                     3/4/2014      115,780       1          
3211               2                     3/4/2014      115,780       1          
3211               1                     2/3/2014      115,520       2          
3211               1                     2/3/2014      115,520       2          

Open in new window

0
 
PortletPaulCommented:
You need RANK() or DENSE_RANK() instead of row_number()

The RANK/DENSE_RANK functions will allow repeating a number (e.g. 4 rows of each with 1) but row_number simply will not do that.

SELECT
        t.Loan_ID
      , t.Mod_Option
      , t.Mod_Date
      , t.Loan_UPB
      , t.row1
FROM (SELECT
                Loan_ID
              , Mod_Option
              , Mod_Date
              , Loan_UPB
              , DENSE_RANK() OVER (PARTITION BY Loan_ID ORDER BY CAST(a.Mod_Date as date) DESC) AS row1
        FROM Exhibit1
     ) t

Open in new window

Note I have used CAST(... as date)  to ensure you are dealing with dates and not dates with time, if that column's data is already date then the cast is not necessary.
0
 
maroulatorAuthor Commented:
Thanks for the tips on my using the code block; I will apply them going forward.
0
 
PortletPaulCommented:
Thanks, using embedded images or code blocks mean we can read the whole question without jumping all over the place and downloading files from lots of questions.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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