string comparison, distinct

[bbvic]

I have a question about string comparison.

here is a query for address

select address id, address from address

address id address
----------- -------------------------------------
1 123 madison ave. chicago, IL 60661
2 123 madison ave. chicago, IL 60661

if I want to have only one since it has same address..

how can i do that?

 

[bbvic]

I have a question about string comparison.

here is a query for address

select address id, address from address

address id address
----------- -------------------------------------
1 123 madison ave. chicago, IL 60661
2 123 madison ave. chicago, IL 60661

if I want to have only one since it has same address..
for example,
address id address id_2 address
--------------- ------------------- -------------------------------------
1 2 123 madison ave. chicago, IL 60661

how can i do that?

 

[SantaMufasa]

BBvic,

What is the maximum number of shared addresses might your data contain? Would it not be better to have the output appear as:

Address IDs Address
----------- ----------------------------------
1,2         123 madison ave. chicago, IL 60661

If that is what you want, then the following gives you those results.

First, here are sample data:

select * from address;

ADDRESS_ID ADDRESS
---------- --------------------------------------------
         1 123 madison ave. chicago, IL 60661
         2 123 madison ave. chicago, IL 60661
         9 123 madison ave. chicago, IL 60661
        17 123 madison ave. chicago, IL 60661
         3 50 E. North Temple, Salt Lake City, UT 84101
         7 50 E. North Temple, Salt Lake City, UT 84101
         5 1234 Lombard St., San Francisco, CA 94101

7 rows selected.

Second, here is a query to produce the output I mentioned, above:

col a heading "Address IDs" format a11
col b heading "Address" format a50
select matrix('select address_id from address where address='''
||address||'''')a, address b
from address
group by address;

Address IDs Address
----------- --------------------------------------------
1,2,9,17    123 madison ave. chicago, IL 60661
5           1234 Lombard St., San Francisco, CA 94101
3,7         50 E. North Temple, Salt Lake City, UT 84101

Lastly, here are the "brains" of the above query...the "matrix" function:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||','||incoming;
    end loop;
    return ltrim(hold_result,',');
END;
/

You can use the "matrix" function anytime you want to consolidate multiple rows of data into a single row with a comma-separated string data from the rows with something in common...in this case, "address".

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bbvic]

the maximum number of shared addresses is two.

 

[bbvic]

this is an example,,,

address id address
----------- -------------------------------------
1 123 madison ave. chicago, IL 60661
2 123 madison ave. chicago, IL 60661
3 423 madison ave. chicago, IL 60667
4 523 madison ave. chicago, IL 60667

result will be like

address id address id_2 address
--------------- ------------------- -------------------------------------
1 2 123 madison ave. chicago, IL 60661
3 423 madison ave. chicago, IL 60667
4 523 madison ave. chicago, IL 60667

 

[SantaMufasa]

In that case, then the following code gives you what you want.

First, here is the revised set of rows:

select * from address;

ADDRESS_ID ADDRESS
---------- --------------------------------------------
         1 123 madison ave. chicago, IL 60661
         2 123 madison ave. chicago, IL 60661
         3 50 E. North Temple, Salt Lake City, UT 84101
         7 50 E. North Temple, Salt Lake City, UT 84101
         5 1234 Lombard St., San Francisco, CA 94101

Here is the query code to give you the results you specified:

set linesize 200
col a heading "address_id_1" format 999999
col b heading "address_id_2" format 999999
col c heading "Address" format a50
select min(address_id) a
      ,to_number(decode(max(address_id)
                       ,min(address_id),null
                       ,max(address_id))) b
      ,address c
  from address
 group by address;

address_id_1 address_id_2 Address
------------ ------------ --------------------------------------------
           1            2 123 madison ave. chicago, IL 60661
           5              1234 Lombard St., San Francisco, CA 94101
           3            7 50 E. North Temple, Salt Lake City, UT 84101
**********************************************************************

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]