Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
how can i get the last record for a particular sequence num

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Fri Sep 11, 2009 10:11 am    Post subject: how can i get the last record for a particular sequence num
Reply with quote

hi ,

for a particular sequece number i have to retrieve the last record from the table is there any function which i can use in my query..

for example

i have ICN number

ICN PAID DATE
01 01-01-2008
01 05-01-2008
02 04-05-2008
02 02-01-2008
02 01-01-2008
03 01-01-2008
03 01-12-2007


for the above data ...... i shud get the result as
01 05-01-2008
02 01-01-2008
03 01-12-2007

Please help me in this regard
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Sep 11, 2009 11:46 am    Post subject:
Reply with quote

There is nothing like a first row/last row unless you use a proper order by clause.
What I mean is, you cannot really say
Code:
01 05-01-2008

is your last record.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 11, 2009 12:37 pm    Post subject:
Reply with quote

until DB2 V9 with OLAP functionality ROW_NUMBER() is not available.

There is a way, but very unperformant
Code:
Select coalesce((select count(distinct t2.paid_date) from table1 T2
           where t2.paid_date > t1.paid_date),0) + 1 as rownum
        , paid_date
 from (select distinct paid_date from table1) T1
order by paid_date desc
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 11, 2009 3:33 pm    Post subject:
Reply with quote

If you really want the last like you state in your first sentence ,
you can : select max(paid_date) from table1

If you want the result like you state in your last sentence , use the sql from my first answer.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Fri Sep 11, 2009 4:36 pm    Post subject:
Reply with quote

You can use this small trick:

Code:
Select abs(Nicn),  PAID DATE
from
(distinct ICN * (-1) as Nicn,  PAID DATE from table1) p1


Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 11, 2009 4:46 pm    Post subject:
Reply with quote

sorry I completely misread the question : you wanted the last date for each icn ? (that's still not what your requested results look like)
but this is how :
Code:
select icn, paid_date
  from table1 t1
 where paid_date = (select max(t2.paid_date)
                     from table1 t2
                    where t2.icn = t1.icn)


hopefullly you have an index on icn or even better : on (icn,paid_date)
Back to top
View user's profile Send private message
bnveena

New User


Joined: 29 May 2007
Posts: 50
Location: hyderabad

PostPosted: Fri Sep 11, 2009 5:13 pm    Post subject: Reply to: how can i get the last record for a particular seq
Reply with quote

Hey i want to be clear that while retrieving data from QMF and retrieving using the IKJEFT01 utility is it the same or not......

please can u clarify this doubt to me....
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Fri Sep 11, 2009 5:35 pm    Post subject:
Reply with quote

GuyC wrote:
sorry I completely misread the question : you wanted the last date for each icn ? (that's still not what your requested results look like)
but this is how :
Code:
select icn, paid_date
  from table1 t1
 where paid_date = (select max(t2.paid_date)
                     from table1 t2
                    where t2.icn = t1.icn)


hopefullly you have an index on icn or even better : on (icn,paid_date)


I made mistake as well.
You are on a right way, but I prefer:

Code:
select icn, paid_date
  from table1 t1 t1
join table
(select t1.icn, max(paid_date)  Mpaid_date
  from table1 t1 t2
  where t2.icn = t1.icn ) t3
on t1.paid_date = t3.Mpaid_date


Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 11, 2009 5:45 pm    Post subject:
Reply with quote

lkhiger wrote:
I prefer:
Code:
select icn, paid_date
  from table1 t1
join table
(select t1.icn, max(t1.paid_date)  Mpaid_date
  from table1 t2
  where t2.icn = t1.icn ) t3
on t1.paid_date = t3.Mpaid_date



I don't see any benefit with coding it your style.

Certainly the accesspath will not be better, rather the opposite.
AFAIK DB2 Optimizer recognizes
"where col = (select max(col) from correlated subselect)" structures and will not actually have to resolve the subselect.

With your "table (subselect) " structure I'm pretty sure it creates a temp table for each T1.icn
[/quote]
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us