Home » SQL & PL/SQL » SQL & PL/SQL » Needs get one row.
Needs get one row. [message #37568] Thu, 14 February 2002 08:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Re: ORA-00937: not a single-group group function
Next Topic: Execute Immediate n ..
Goto Forum:
  


Current Time: Thu Mar 28 17:23:41 CDT 2024