| SQL beeded [message #217073] |
Wed, 31 January 2007 10:18  |
roopla
Messages: 52
Registered: May 2006
|
Member |
|
|
I have a table in the following format. All I have to find out the states with the same addr_id. The sql shoud return with three fields with the following data
IA WV 100
IA VA 200
Primary key on this table is combination of both STATE_CODE and ADDR_ID. Please help me in writing most effiecient sql as it involves lots of data
----------------------
create table ST_ADDR
(
STATE_CODE VARCHAR2(2),
ADDR_ID NUMBER(11) not null
)
INSERT VALUES INTO ST_ADDR('IA',100);
INSERT VALUES INTO ST_ADDR('IA',200);
INSERT VALUES INTO ST_ADDR('IA',300);
INSERT VALUES INTO ST_ADDR('WV',100);
INSERT VALUES INTO ST_ADDR('VA',200);
INSERT VALUES INTO ST_ADDR('TX',600);
|
|
|
|
|
|
| Re: SQL beeded [message #217082 is a reply to message #217073] |
Wed, 31 January 2007 10:52  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
It depends on how many duplicate addr_ids you have. If there are never more than two, the following would work:
select state_code, next_state_code, addr_id
from
(select state_code, lead(state_code) over (partition by addr_id order by state_code) as next_state_code, addr_id
from st_addr)
where next_state_code is not null
If you can have more than two, it's problematic because you would need a variable number of columns.
[Updated on: Wed, 31 January 2007 10:55] Report message to a moderator |
|
|
|
| Re: SQL beeded [message #217084 is a reply to message #217082] |
Wed, 31 January 2007 11:03 |
roopla
Messages: 52
Registered: May 2006
|
Member |
|
|
It works like a champ.
Could you elaborate on this syntax, if possible
lead(state_code) over (partition by addr_id order by state_code)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
