Home » SQL & PL/SQL » SQL & PL/SQL » SQL beeded
SQL beeded [message #217073] Wed, 31 January 2007 10:18 Go to next message
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 #217079 is a reply to message #217073] Wed, 31 January 2007 10:44 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi,

give sample data of desired output...

Regards
Prashant Pathak
Re: SQL beeded [message #217082 is a reply to message #217073] Wed, 31 January 2007 10:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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)
Re: SQL beeded [message #217086 is a reply to message #217073] Wed, 31 January 2007 11:05 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
There's a reasonably good link on this site on analytic functions:

http://www.orafaq.com/node/55
Previous Topic: Help with query (first_value)
Next Topic: NLS SETTINGS PROBLEM
Goto Forum:
  


Current Time: Sun Dec 04 22:51:27 CST 2016

Total time taken to generate the page: 0.07305 seconds