OraFAQ Forum: SQL & PL/SQL » Query the newest records

Home » SQL & PL/SQL » SQL & PL/SQL » Query the newest records (oracle11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query the newest records [message #634736]

Sat, 14 March 2015 05:04

zhaoquer
Messages: 35
Registered: October 2013

Member

I want to query the newest record according to the pid.
Datas in the table:
id name pid date
1 n1 001 2015-03-12 10:00
2 n1 001 2015-03-12 11:00
3 n1 002 2015-03-12 10:00
4 n1 002 2015-03-12 11:00

Expected Result:
id name pid date
2 n1 001 2015-03-12 11:00
4 n1 002 2015-03-12 11:00
Please give me a advise , thanks in advance.

Report message to a moderator

Re: Query the newest records [message #634738 is a reply to message #634736]

Sat, 14 March 2015 05:19

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Newest employee in each department:

SQL> with 
  2    data as (
  3      select ename, deptno,
  4             rank () over (partition by deptno order by hiredate desc) rk
  5      from emp
  6    )
  7  select deptno, ename
  8  from data
  9  where rk = 1
 10  order by 1, 2
 11  /
    DEPTNO ENAME
---------- ----------
        10 MILLER
        20 ADAMS
        30 JAMES

3 rows selected.

[Updated on: Sat, 14 March 2015 05:21]

Report message to a moderator

Re: Query the newest records [message #634739 is a reply to message #634738]

Sat, 14 March 2015 05:47

zhaoquer
Messages: 35
Registered: October 2013

Member

Michel, thanks a lot.That solve my problem.

create table tab0314(
id varchar2(10),
name varchar2(20),
pid varchar2(10),
operDate date
);

insert into tab0314 values ('1','n1','001',to_date('2015/03/12 10:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into tab0314 values ('2','n1','001',to_date('2015/03/12 11:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into tab0314 values ('3','n1','002',to_date('2015/03/12 10:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into tab0314 values ('4','n1','002',to_date('2015/03/12 11:00:00','yyyy-mm-dd hh24:mi:ss'));
commit;

with
data as (
select id,name, pid,operDate,
rank () over (partition by pid order by operDate desc) rk
from tab0314
)
select id,name, pid,operDate
from data
where rk = 1
order by 1, 2;

Report message to a moderator

Re: Query the newest records [message #634741 is a reply to message #634739]

Sat, 14 March 2015 07:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback but Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Previous Topic:	ORA-01427: single-row subquery returns more than one row
Next Topic:	Query execution changes in oracle 11.2.0.4.5 version

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 01:26:26 CDT 2026