get a anr record from databse by using max (one Field) and orderby (one filed) desc
Home > IT Answers > Database > get a anr record from databse by using max (o...
Workingonswingsanddatabase
5 pts.
0
Q:
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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
FlaviusMaximus
180 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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 Answered: Oct 22 2009  6:49 PM GMT by FlaviusMaximus   180 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29770 pts.  |   Oct 21 2009  2:37PM GMT

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 ?

 
0