CODEWITHSUNDEEP
sqldatabasecompareresultset
Thevagabond
Thevagabond

Reputation: 323

Compare SQL result rows with each other

I have a result set from a sql query that I need to compare to each other.

Example:

ID |  VALUE  |  Comment | sort | store | price
 1    test       main      des    f1       5
 2    each       sec       asc    f2       10
 3    test       main      des    f1       12

Now from that result set I need to only get the rows where value, comment, sort and store are the same.

like:

 ID |  VALUE  |  Comment | sort | store | price
  1    test       main      des     f1      5
  3    test       main      des     f1      12

so I need to alter the 

select id, value, comment, sort, store, price from test.table

and do that matching.

Any ideas on how I can do that?

Thanks in advance.

TheVagabond

Upvotes: 0

Views: 2713

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Most SQL databases support window functions. You can do this as:

select id, value, comment, sort, store, price
from (select t.*,
             count(*) over (partition by value, comment, sort, store) as cnt
      from t
     ) t
where cnt > 1;

Upvotes: 2

Stan
Stan

Reputation: 1999

If your database does not support window functions you can try the query below:

select
    *
from
    (
        select
            value, comment, sort, store
        from
            test
        group by
            value, comment, sort, store
        having count(*) > 1
    ) as t
    inner join test on (
        test.value = t.value and test.sort = t.sort and test.сomment = t.сomment and test.store = t.store
    )

But I'd suggest you another output for "each other" comparison:

select
    t1.id, t2.id, t1.VALUE, t1.sort, t1.store, t1.price, t2.price
from
    test t1
    join test t2 on (t2.id > t1.id and t1.value = t2.value and t1.sort = t2.sort and t1.store = t2.store and t1.comment = t2.comment)

Upvotes: 1

Related Questions