get a anr record from databse by using max (one Field) and orderby (one filed) desc

Tags:
Database issues
JOIN statement
Oracle Database
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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 ?
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following