Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help
SQL query help [message #383314] Wed, 28 January 2009 04:12 Go to next message
nicky
Messages: 5
Registered: January 2009
Junior Member
Good day.

I need to write a sql query to retrieve the role of a person where the role is 'PP' but only the latest record(ie. latest date_to). could you please assist. The data is like below and the dates are not known. Thanks in advance.

num             role      policy      pol_type   date_from        date_to
1000000001	IPV	5001000000	VITA	01/01/2003	28/02/2005
1000000001	PP	5001000000	VITA	01/01/2003	28/02/2005
1000000001	PP	5001000000	VITA	01/04/2005	28/02/2006

[Updated on: Wed, 28 January 2009 05:29] by Moderator

Report message to a moderator

Re: SQL query help [message #383316 is a reply to message #383314] Wed, 28 January 2009 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Somehow I suspect you wnat to retrieve more than just the role.
Anyway - you'll be wanting to use a correlated subquery to get the max date for the current person.
Have a look in the documentation for correlated subquey and if you get stuck post back here with what you tried and a test case.
Re: SQL query help [message #383317 is a reply to message #383314] Wed, 28 January 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel

[Updated on: Wed, 28 January 2009 04:39]

Report message to a moderator

Re: SQL query help [message #383321 is a reply to message #383314] Wed, 28 January 2009 05:14 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

You can use
select role, max(date_to)
from table
group by role

Thanks

Pardeep Sharma
Re: SQL query help [message #383324 is a reply to message #383314] Wed, 28 January 2009 05:25 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Try this


select a.role
from (
select role,date_to
from test
where role = 'PP'
order by date_to desc) a
where rownum<=1
Re: SQL query help [message #383325 is a reply to message #383314] Wed, 28 January 2009 05:30 Go to previous messageGo to next message
nicky
Messages: 5
Registered: January 2009
Junior Member
Hi, I have tried the following code. Please tell me if it looks correct.. Thanks.

select role from entity_table where entity_no=1000000001 and policy_no=5001000000
     and (entity_role='PP' or entity_role='SP' or entity_role='AD' or entity_role='CH')
     and date_to= (select max(date_to) from entity_table where entity_no=1000000001 and policy_no=5001000000
                       and (role='PP' or role='SP' or role='AD' or role='CH'))
Re: SQL query help [message #383334 is a reply to message #383314] Wed, 28 January 2009 06:07 Go to previous message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
It looks like it will do the job.
Few points:
This:
and (entity_role='PP' or entity_role='SP' or entity_role='AD' or entity_role='CH')

Can be rewritten as:
and entity_role IN ('PP', 'SP', 'AD', 'CH')


A correlated subquery references the outer query.
Which would look something like this:
SELECT role 
FROM entity_table eta 
WHERE eta.entity_no = 1000000001 
AND eta.policy_no = 5001000000
AND eta.date_to = (SELECT max(etm.date_to) 
                   FROM entity_table etm 
                   WHERE etm.entity_no = eta.entity_no 
                   AND etm.policy_no = etm.policy_no)


You should have a read of the pages Michel linked to above, that method is probably better.
Previous Topic: Triggers fail to fire (merged 3)
Next Topic: Compare records
Goto Forum:
  


Current Time: Wed Feb 12 04:58:44 CST 2025