get a anr record from databse by using max (one Field) and orderby (one filed) desc
Hi,
I have a table. It can have multiple records for the key value. Ex
secid time source
1 02:04 a
1 02:04 b
2
3
Now i want only one record of secid for a descending orderby of (source) and for a max time stamp.
and later i will join the secid with the other table as a join.
I tried with orderby with rownum=1 option its not working.
Please let me know u t ideas.
Software/Hardware used:
Oracle
I have a table. It can have multiple records for the key value. Ex
secid time source
1 02:04 a
1 02:04 b
2
3
Now i want only one record of secid for a descending orderby of (source) and for a max time stamp.
and later i will join the secid with the other table as a join.
I tried with orderby with rownum=1 option its not working.
Please let me know u t ideas.
Software/Hardware used:
Oracle
ASKED:
October 20, 2009 1:12 PM
UPDATED:
October 22, 2009 6:49 PM
Answer Wiki:
There are several ways to do this using basic MAX and GROUP BY. Here are a couple of examples.
<pre>create table temp1
(
secid number
, atimestamp date
, acode varchar2(1)
)
/
insert into temp1 values (1,sysdate-1,'a');
insert into temp1 select secid,atimestamp,'b' from temp1;
alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';
select * from temp1;
select *
from temp1
where (secid,acode||':'||atimestamp) in (
select secid,max(acode||':'||atimestamp)
from temp1
group by secid
)
/
select *
from temp1
where (secid,atimestamp,acode) in (
select secid,max(atimestamp),acode
from temp1
where (secid,acode) in (
select secid,max(acode)
from temp1
group by secid
)
group by secid,acode
)
/</pre>
Good luck, Kevin
To see all answers submitted to the Answer Wiki: View Answer History.
When you want to use the ‘rownum’ to achieve something like that, you need to use inline views.
Something like this:
But I guess you need one row per secid…
What version of the database are you using ?