SQL query help [message #383314] |
Wed, 28 January 2009 04:12  |
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   |
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 #383325 is a reply to message #383314] |
Wed, 28 January 2009 05:30   |
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  |
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.
|
|
|