CODEWITHSUNDEEP
sql
DAC
DAC

Reputation: 41

SQL Optimization - Match Analysis sorting matched pairs

Scenario : Matching algorithm has identified ID1 AND ID2 have matched.I need to do further analysis on the matching. For that I need to reduce the number of rows in output and sorted correctly.

This input is just sample and subset. Having thousands of actual records makes this task difficult.

INPUT:

ID1 NAME1 ID2 NAME2
222 SIM 333 SIM
111 SAM 222 SIM
111 SAM 333 SIM
111 SAM 444 SOM
111 SAM 555 SAM
222 SIM 444 SOM
222 SIM 555 SAM
333 SIM 444 SOM
444 SOM 555 SAM
013 AAA 014 BBB
021 SUB 111 SAM
010 CCC 011 DDD
023 SOB 333 SIM

EXPECTED OUTPUT:

ID NAME
111 SAM
222 SIM
333 SIM
444 SOM
555 SAM
021 SUB
023 SOB
013 AAA
014 BBB
010 CCC
011 DDD

I need to ensure that output should have ID should have distinct records of ID1 and ID2 combined which is still fine as I can do distinct and union.

Tricky part is to ensure sorting of data in ouptput. I need to keep the rows that are similar in order.

Example :

111,222,333,444,555,021,023 have similar matching ID's in ID1 and ID2 and have to be sorted together. Within this group, the sorting order doesn't matter, just they need to be together. Similarly there could be many such groups.

The rest whenever only 1 pair is there, just need to sort them together like 013,014 and 010,011 and so on

Can anyone help me with this query?

Upvotes: 1

Views: 73

Answers (1)

Ísis Santos Costa
Ísis Santos Costa

Reputation: 391

For the case of knowing the patterns a priori, union + order by case when would go here:

select * from (
select ID1, NAME1 from tab1 union
select ID2, NAME2 from tab1 ) a
order by case when NAME1 like '%S' then '' else NAME1 end;

SQL Fiddle on your data here

To group based on just ID1 & ID2, added a sorter column, concatenating the IDs of each row with least & greatest on MySQL:

select * from (
  select concat(least(ID1, ID2), greatest(ID1,ID2)) sorter, ID1, NAME1 from tab1 union
  select concat(least(ID1, ID2), greatest(ID1,ID2)) sorter, ID2, NAME2 from tab1 ) a
 group by ID1
 order by sorter

or with case when on SQL Server:

select min(sorter) sorter, ID1, NAME1 from (
  select concat(case when ID1<ID2 then ID1 else ID2 end, case when ID1>ID2 then ID1 else ID2 end) sorter, ID1, NAME1 from tab1 union
  select concat(case when ID1<ID2 then ID1 else ID2 end, case when ID1>ID2 then ID1 else ID2 end) sorter, ID2, NAME2 from tab1 ) a
 group by ID1, Name1
 order by min(sorter)

Upvotes: 0

Related Questions