SQL find entire row where only 2 columns values

Question

I'm attempting to

select columns Age, Height, House_number, Street
from my_table
where count(combination of House_number, Street)
occurs more than once.

My table looks like this

Age, Height, House_number, Street
15   178     6             Mc Gill Crst 
85   166     6             Mc Gill Crst
85   166     195           Mc Gill Crst
18   151     99            Moon Street 
52   189     14a           Grimm Lane

My desired outcome looks like this

Age, Height, House_number, Street
15   178     6             Mc Gill Crst 
85   166     6             Mc Gill Crst

Stuck!

What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `SQL Server`? `MySQL`? `Oracle`? `DB2`? etc.. — John Woo, Feb 21 '13 at 23:14

Gordon Linoff · Accepted Answer · 2013-02-22T00:01:06.300

6

The best way to do this is with window functions, assuming your database supports them:

select columns Age, Height, House_number, Street
from (select t.*, count(*) over (partition by house_number, street) as cnt
      from my_table t
     ) t
where cnt > 1

This is using a windows function (also called analytic function) in Oracle. The expression count(*) over (partition by house_number, street) is counting the number of rows for each house_number and street combination. It is kind of like doing a group by, but it adds the count to each row rather than combining multiple rows into one.

Once you have that, it is easy to simply choose the rows where the value is greater than 1.

edited Feb 22 '13 at 00:01

answered Feb 21 '13 at 23:06

Gordon Linoff

1,242,037
58
646
786

Many thanks for the quick response. Oracle is my RDBMS. Issue resolved! I'm not sure I understand what the "m.*, count(*) over (partition by ID_VALUE, ISSUING_AUTHORITY)" is doing. Can you elucidate if you have time? Perhaps point me toward a resource that will expand my understanding? – Nimbocrux Feb 21 '13 at 23:17

John Woo · Answer 2 · 2013-02-21T23:19:55.893

2

Since you haven't mentioned the RDBMS you are using, the query below will amost work on most RDBMS.

SELECT  *
FROM    tableName
WHERE   (House_number, Street) IN
(
    SELECT House_number, STREET
    FROM tableName
    GROUP BY House_number, STREET
    HAVING COUNT(*) >= 2
)

SQLFiddle Demo

edited Feb 21 '13 at 23:19

answered Feb 21 '13 at 23:06

John Woo

258,903
69
498
492

@DaleM *..where only 2 columns values* -- that's my interpretation `:D` – John Woo Feb 21 '13 at 23:08
I think that OP meant `combination of House_number, Street`. – Nikola Markovinović Feb 21 '13 at 23:11
This will not work if there is a house with a matching `house_number` -- see this demo -- http://sqlfiddle.com/#!2/2e6d4/1. You need to check for both the `house_number` and the `street` – Taryn Feb 21 '13 at 23:18
@bluefeet yah, i mess up with this. But i still updated the answer. – John Woo Feb 21 '13 at 23:21

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

Sounds like you need a NOT DISTINCT. The following might give you what you need: Multiple NOT distinct

edited May 23 '17 at 11:44

Community

1
1

answered Feb 21 '13 at 23:15

James Oravec

19,579
27
94
160

score 0 · Answer 4 · answered Feb 21 '13 at 23:16

If you do not have windowing function, then you can use a subquery with a JOIN. The subquery gets the list of the house_number and street that have a count of greater than 1, this result is then used to join back to your table:

select t1.age,
  t1.height,
  t1.house_number,
  t1.street
from my_table t1
inner join
(
  select house_number, street
  from my_table 
  group by house_number, street
  having count(*) > 1
) t2
  on t1.house_number = t2.house_number
  and t1.street = t2.street

See SQL Fiddle with Demo

SQL find entire row where only 2 columns values

4 Answers4