Return rows in a table where several columns are equal

Question

I have the below table:

I'm trying to write some SQL to return Row IDs (in pairs) where the below is true for two given rows:

Item is equal
Name is equal
Measure is equal
Dates overlap

So far what I have tried to do is use SELECT, WHERE & GROUP BY to return Row IDs (I'm aware this lacks the date functionality at present):

SELECT rowid FROM techtest GROUP BY Customer, Product, Measure;

However this returns individual Row IDs instead of pairs. For example what I hope to get as a return is Row ID 1 & 2 together, as they meet all of the criteria listed.

How would I go about writing this to return a list of pairs of rows which meet the criteria listed?

Your question confuses me. You want to select 1, 2 and 3 together because item, name and measure is equal and dates overlap, but how do you expect to return them? 4, 5 and 6 also meet your criteria. Do you want 2 rows with 1 column saying `1, 2, 3` and `4, 5, 6` respectively? How do you want them grouped? — Kars, Mar 17 '19 at 00:27

GMB · Accepted Answer · 2019-03-17T01:02:57.367

One solution to generate the expected resultset would be to self-JOIN the table:

SELECT t1.row_id, t2.row_id
FROM mytable t1
INNER JOIN mytable t2 
    ON  t1.item    = t2.item
    AND t1.name    = t2.name
    AND t1.measure = t2.measure
    AND t1.begin_date  <= t2.expiry_date 
    AND t1.expiry_date >= t2.begin_date
    AND t1.row_id  < t2.row_id

The logic lies in the ON conditions of the JOIN :

t1.begin_date <= t2.expiry_date AND t1.expiry_date >= t2.begin_date represents the date range overlap condition (see this SO post for more details) - depending on your RDBMS, there might be more straight-forward solutions available
t1.row_id < t2.row_id avoids duplicates in the results like (1, 2, 2, 1)

score 0 · Answer 2 · answered Mar 17 '19 at 00:30

Not sure if understood correctly, but this shows how you could proceed:

create table trash ( 
 id integer, 
 ticket text, 
 name text);

insert into trash values(1,'ticket','name1'),(2,'ticket','name1');
insert into trash values(3,'billet','name2'),(4,'billet','name2');

select * from trash 

 ;                                                                                                                                                                                                                                           
+------+----------+--------+
| id   | ticket   | name   |
|------+----------+--------|
| 1    | ticket   | name1  |
| 2    | ticket   | name1  |
| 3    | billet   | name2  |
| 4    | billet   | name2  |
+------+----------+--------+

select distinct(array[least(trash.id,t1.id), greatest(trash.id, t1.id)]) 
from (select * from trash) t1 
left join trash on t1.ticket=trash.ticket 
and t1.name=trash.name 
and trash.id != t1.id;   

+---------+
| array   |
|---------|
| [3, 4]  |
| [1, 2]  |
+---------+

Return rows in a table where several columns are equal

2 Answers2