Home » SQL & PL/SQL » SQL & PL/SQL » Struggeling on query......
Struggeling on query...... [message #187761] Tue, 15 August 2006 07:47 Go to next message
ronnie76
Messages: 1
Registered: August 2006
Junior Member
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

[Updated on: Tue, 15 August 2006 08:04]

Report message to a moderator

Re: Struggeling on query...... [message #187783 is a reply to message #187761] Tue, 15 August 2006 10:35 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Search the board for "duplicate rows"
Re: Struggeling on query...... [message #187787 is a reply to message #187761] Tue, 15 August 2006 11:13 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Couple of hints:

1) You'll want to first identify the house/zip combinations that appear more than once. Look at the GROUP BY and HAVING clauses - the HAVING would have something to do with perhaps a count condition.

2) Once you have those combos, you can find the IDs by looking for rows that have a house/zip combo IN the above list.
Re: Struggeling on query...... [message #187800 is a reply to message #187761] Tue, 15 August 2006 13:18 Go to previous message
Nirmala
Messages: 43
Registered: October 2004
Member
I think the following should help you.

SQL> select * from test
2 /

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

8 rows selected.

SQL> select id
2 from test a ,
3 (select house#, zip, count(*)
4 from test
5 group by house#,zip
6 having count(*) > 1) b
7 where a.house#=b.house#
8 and a.zip=b.zip;

ID
----------
1
3
5
4
Previous Topic: simple plsql
Next Topic: Need help in materialized views/snapshot
Goto Forum:
  


Current Time: Wed Dec 07 14:21:13 CST 2016

Total time taken to generate the page: 0.08928 seconds