Needs get one row. [message #37568] |
Thu, 14 February 2002 08:30 |
sopao
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
Hi guys,
i have a resultset as follow:
cod date
--------------
01 01/01/2001
01 01/02/2002
02
02 01/01/1980
03 08/08/1950
03 09/09/1980
..
..
..
I need get a record with last date in group of cod=01 and last date in group of cod=02 and so on.
how can i make it using PL/SQL?
Thanks a lot.
Krikor
|
|
|
Re: Needs get one row. [message #37571 is a reply to message #37568] |
Thu, 14 February 2002 10:19 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I'm not sure why you would want to do this in PL/SQL (loop through the result set, figure out when the cod changes, when it does - the last date saved in a variable is the max date for the previous cod - yuck!) when it would be so much easier to do with a SQL statement:
select cod, max(date)
from t
group by cod
Never do in PL/SQL what you can do far easier in SQL.
|
|
|
Re: Needs get one row. [message #37604 is a reply to message #37568] |
Fri, 15 February 2002 05:29 |
sopao
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
Ok,
but i need others columns with agregate data.
I mean:
I need insert in other table only record wich have a last date (max date is ok) but i need others columns too...
cod date number name .....
01 01/01/2002 01 mary
01 02/02/2002 02 john
02 09 Paul
03 01/01/1980 03 Sam
03 04 Sam
May be its more little bit clear.
Thanks.
Krikor
|
|
|
Re: Needs get one row. [message #37606 is a reply to message #37604] |
Fri, 15 February 2002 08:16 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I will assume then that those other columns that you want are on the row that has the max date for a cod.
So, you could:
insert into some_other_table
(cod, date, number, name)
select cod, date, number, name
from t
where (cod, date) in
(select cod, max(date)
from t
group by t);
This will include rows where the date is the maximum date for that cod.
|
|
|