Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Query needed

rhemminkrhemmink Member Posts: 16
Hi there,

I have a table looking like this:

ID house# ZIP
1 576 5000AN
2 576 4000AN
3 576 5000AN
4 576 2000AN
5 576 2000AN
6 300 1111ZZ
7 301 1111ZZ
8 200 9999EE

Now I want the ID's from every row that has an appearence of > 1 and having a different ID and having the same house and ZIP as another row.

In this case I would retrieve the ID's:

1,3,4,5
Please help me out because I'm stuck on this one

Thanks,

Ronald

Comments

  • infidelinfidel Member Posts: 2,900
    [b][red]This message was edited by infidel at 2006-8-15 8:0:32[/red][/b][hr]
    : Hi there,
    :
    : I have a table looking like this:
    : [code]
    : ID house# ZIP
    : 1 576 5000AN
    : 2 576 4000AN
    : 3 576 5000AN
    : 4 576 2000AN
    : 5 576 2000AN
    : 6 300 1111ZZ
    : 7 301 1111ZZ
    : 8 200 9999EE
    : [/code]
    : Now I want the ID's from every row that has an appearence of > 1 and having a different ID and having the same house and ZIP as another row.
    :
    : In this case I would retrieve the ID's:
    :
    : 1,3,4,5
    : Please help me out because I'm stuck on this one

    Try this and let me know if it works:

    [code]
    select id
    from tablename t
    inner join
    (
    select house_number, zip
    from tablename
    group by house_number, zip
    having count(*) > 1
    ) t2 on (
    t.house_number = t2.house_number and
    t.zip = t2.zip
    )
    [/code]




    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]



  • FunmiladeFunmilade Member Posts: 15
    : Hi there,
    :
    : I have a table looking like this:
    :
    : ID house# ZIP
    : 1 576 5000AN
    : 2 576 4000AN
    : 3 576 5000AN
    : 4 576 2000AN
    : 5 576 2000AN
    : 6 300 1111ZZ
    : 7 301 1111ZZ
    : 8 200 9999EE
    :
    : Now I want the ID's from every row that has an appearence of > 1 and having a different ID and having the same house and ZIP as another row.
    :
    : In this case I would retrieve the ID's:
    :
    : 1,3,4,5
    : Please help me out because I'm stuck on this one
    :
    : Thanks,
    :
    : Ronald
    :

    Hi Ronald,

    I created a table called 'ronald' and populated it with your test data. The following query achieves your result:

    SELECT t.ID, t.HOUSE, t.ZIP
    FROM ronald t, ronald tt
    WHERE t.ID <> tt.ID AND t.ZIP = tt.ZIP AND t.house = tt.house;

    I hope you find it useful.

    Cheers,
    Funmilade
  • ssvasanssvasan Member Posts: 101
    : Hi there,
    :
    : I have a table looking like this:
    :
    : ID house# ZIP
    : 1 576 5000AN
    : 2 576 4000AN
    : 3 576 5000AN
    : 4 576 2000AN
    : 5 576 2000AN
    : 6 300 1111ZZ
    : 7 301 1111ZZ
    : 8 200 9999EE
    :
    : Now I want the ID's from every row that has an appearence of > 1 and having a different ID and having the same house and ZIP as another row.
    :
    : In this case I would retrieve the ID's:
    :
    : 1,3,4,5
    : Please help me out because I'm stuck on this one
    :
    : Thanks,
    :
    : Ronald
    :



    Hi,

    Please find below the example code that will help you getting desired results.,

    create table home1(
    id number(2),
    dno number(3),
    zip varchar2(6));

    insert into home1 values(1, 576, '5000AN');
    insert into home1 values(2, 576, '4000AN');
    insert into home1 values(3, 576, '5000AN');
    insert into home1 values(4, 576, '2000AN');
    insert into home1 values(5, 576, '2000AN');
    insert into home1 values(6, 300, '1111ZZ');
    insert into home1 values(7, 301, '1111ZZ');
    insert into home1 values(8, 200, '9999EE');


    select distinct id from home1
    where (dno, zip) in(select dno, zip from home1
    group by dno, zip having count(*)>1);


    - Maha.


Sign In or Register to comment.