Home > IT Answers > Database > get a anr record from databse by using max (o...
Workingonswingsanddatabase
5 pts.
 get a anr record from databse by using max (one Field) and orderby (one filed) desc
Database issues, Oracle Database, JOIN statement
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
ASKED: Oct 20, 2009  1:12 PM GMT
UPDATED: October 22, 2009  6:49:10 PM GMT
RELATED QUESTIONS
FlaviusMaximus
220 pts.
Answer Wiki:
There are several ways to do this using basic MAX and GROUP BY. Here are a couple of examples.

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
                                   )
 /

Good luck, Kevin
Last Wiki Answer Submitted:  Oct 22, 2009  6:49 PM (GMT)  by  FlaviusMaximus   220 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

When you want to use the ‘rownum’ to achieve something like that, you need to use inline views.

Something like this:

SELECT *
FROM
	(SELECT secid, time, source
	FROM your_table
	WHERE secid = 1
	ORDER BY time desc, source desc)
WHERE rownum = 1;

But I guess you need one row per secid…

What version of the database are you using ?

Carlosdl
 60,245 pts.