Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

teradata sql olap functions

I would like to return the last record in a group and the previous row proceeding the last row.  I was wondering if anyone new how to use ranking or partioning to solve this problem in sql statement.
example 1
member       date        amount
1111            01/14         20.00
1111            02/14         15.00
1111            03/14         50.00
2222            07/13         10.00
2222            01/14         15.00
3333            01/08         20.00
3333            01/09         50.00
3333            01/12         75.00
3333            01/13         80.00

Results
member       date        amount
1111            02/14         15.00
1111            03/14         50.00

2222            07/13         10.00
2222            01/14         15.00

3333            01/12         75.00
3333            01/13         80.00
0
centralmike
Asked:
centralmike
1 Solution
 
SharathData EngineerCommented:
try this.
SELECT t2.member,t2.date,t2.amount
  FROM (SELECT t1.*,
			   ROW_NUMBER() OVER (PARTITION BY t1.member ORDER BY t1.date DESC)
		  FROM your_table t1) t2
 WHERE t2.rn <= 2

Open in new window

0
 
lcohanDatabase AnalystCommented:
You mean something like this?


create table test_table (member int, crtdate Date, amount money)

insert into test_table select 1111, '01/01/14', 20.00
insert into test_table select 1111, '02/01/14', 15.00
insert into test_table select 1111, '03/01/14', 50.00

insert into test_table select 2222, '07/01/13', 10.00
insert into test_table select 2222, '01/01/14', 15.00
insert into test_table select 3333, '01/01/08', 20.00
insert into test_table select 3333, '01/01/09', 50.00
insert into test_table select 3333, '01/01/12', 75.00
insert into test_table select 3333, '01/01/13', 80.00
 
select * from
      (  
            SELECT t2.rn,t2.member,t2.crtdate,t2.amount
              FROM (SELECT t1.member,t1.crtdate,t1.amount,
                                 ROW_NUMBER() OVER (PARTITION BY t1.member ORDER BY t1.crtdate) as rn
                          FROM test_table t1) t2
      ) tl            
WHERE rn > (select (max(rn) - 2) from
            (  
            SELECT t2.rn,t2.member,t2.crtdate,t2.amount
              FROM (SELECT t1.member,t1.crtdate,t1.amount,
                                 ROW_NUMBER() OVER (PARTITION BY t1.member ORDER BY t1.crtdate) as rn
                          FROM test_table t1) t2
            ) b
WHERE member = tl.member GROUP BY member)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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