|
View previous topic :: View next topic
|
| Author |
Message |
bnveena
New User
Joined: 29 May 2007
Posts: 50
Location: hyderabad
|
|
|
|
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 |
|
 |
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
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
is your last record. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
bnveena
New User
Joined: 29 May 2007
Posts: 50
Location: hyderabad
|
|
|
|
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 |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
|
|
