grouping related rows

[iamready]

Dear all,
I have the following data
col1 col2 col3 col4
a decimal value decimal value decimal value
b decimal value decimal value decimal value
c decimal value decimal value decimal value
d decimal value decimal value decimal value
e decimal value decimal value decimal value
f decimal value decimal value decimal value
g decimal value decimal value decimal value
h decimal value decimal value decimal value

in the 8 rows above there a 4 twin pairs, all the column values of each pair differ only by 0.5, how do i write a SQL query to identify the twin pairs and group them together.

thank you
harsha

 

[PHV]

Can you please post examples input values and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iamready]

hi the input values are,


Column1 Col2 col3 col4 col5 col6 col7

02C10226 131.52 131.52 170.94 174.94 224.13 236.27

01C07330 131.6 131.6 175.07 183.04 236.32 236.32

HI1330 131.59 131.59 170.94 174.95 220.25 248.44

01C05669 131.43 131.43 170.88 174.83 224.07 236.28

02C09931 131.51 131.51 170.94 174.98 220.06 248.26

01C07329 131.51 131.51 174.96 182.91 236.27 236.27

02C09873 135.8 135.8 179 186.98 236.29 240.3

01C06059 135.68 135.68 178.77 186.71 236.09 240.23

of the 8 rows four are pairs based on the assumption that the column values have a difference of <= 0.5, how do i write a query to find out what the twin pairs are and group them together.

thank you
harsha

 

[PHV]

I don't know what is group them together for you.
Can you please post the expected result with the input values you posted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carp]

And which columns you are comparing to find the .5 difference?!

 

[iamready]

the output should be like

Column1 Col2 col3 col4 col5 col6 col7

02C09873 135.8 135.8 179 186.98 236.29 240.3
01C06059 135.68 135.68 178.77 186.71 236.09 240.23


02C10226 131.52 131.52 170.94 174.94 224.13 236.27
01C05669 131.43 131.43 170.88 174.83 224.07 236.28

01C07330 131.6 131.6 175.07 183.04 236.32 236.32
01C07329 131.51 131.51 174.96 182.91 236.27 236.27

HI1330 131.59 131.59 170.94 174.95 220.25 248.44
02C09931 131.51 131.51 170.94 174.98 220.06 248.26

the twin pairs should be listed next to each other

thank you
harsha

 

[iamready]

all the columns except the first one

harsha

 

[PHV]

With your input values we get 11 different pairs.
The following SQL code:
SELECT A.Column1, A.Col2, A.Col3, A.Col4, A.Col5, A.Col6, A.Col7
,B.Column1, B.Col2, B.Col3, B.Col4, B.Col5, B.Col6, B.Col7
FROM Table7 A, Table7 B
WHERE A.Col2 Between 0.95 * B.Col2 And 1.05 * B.Col2
AND A.Col3 Between 0.95 * B.Col3 And 1.05 * B.Col3
AND A.Col4 Between 0.95 * B.Col4 And 1.05 * B.Col4
AND A.Col5 Between 0.95 * B.Col5 And 1.05 * B.Col5
AND A.Col6 Between 0.95 * B.Col6 And 1.05 * B.Col6
AND A.Col7 Between 0.95 * B.Col7 And 1.05 * B.Col7
AND A.Column1<B.Column1
ORDER BY 1;
Give the following pairs:
[tt]
01C05669 131,43 131,43 170,88 174,83 224,07 236,28 02C09931 131,51 131,51 170,94 174,98 220,06 248,26
01C05669 131,43 131,43 170,88 174,83 224,07 236,28 HI1330 131,59 131,59 170,94 174,95 220,25 248,44
01C05669 131,43 131,43 170,88 174,83 224,07 236,28 02C10226 131,52 131,52 170,94 174,94 224,13 236,27
01C06059 135,68 135,68 178,77 186,71 236,09 240,23 02C09873 135,8 135,8 179 186,98 236,29 240,3
01C06059 135,68 135,68 178,77 186,71 236,09 240,23 01C07329 131,51 131,51 174,96 182,91 236,27 236,27
01C06059 135,68 135,68 178,77 186,71 236,09 240,23 01C07330 131,6 131,6 175,07 183,04 236,32 236,32
01C07329 131,51 131,51 174,96 182,91 236,27 236,27 02C09873 135,8 135,8 179 186,98 236,29 240,3
01C07329 131,51 131,51 174,96 182,91 236,27 236,27 01C07330 131,6 131,6 175,07 183,04 236,32 236,32
01C07330 131,6 131,6 175,07 183,04 236,32 236,32 02C09873 135,8 135,8 179 186,98 236,29 240,3
02C09931 131,51 131,51 170,94 174,98 220,06 248,26 HI1330 131,59 131,59 170,94 174,95 220,25 248,44
02C10226 131,52 131,52 170,94 174,94 224,13 236,27 HI1330 131,59 131,59 170,94 174,95 220,25 248,44
[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iamready]

Dear PHV, this code will not work as the 8 rows i gave are 4 twin pairs, both rows in a twin pair have almost similar values( here the difference is 0.5)for eg

02C09873 135.8 135.8 179 186.98 236.29 240.3
01C06059 135.68 135.68 178.77 186.71 236.09 240.23

leaving the first column elements which are the primary keys, all the other columns are almost the same (difference of 0.5) from this we can say that it is a twin pair , i have a table that has 55 twin pairs randomnly oriented i want to find out the primary key values of each twin pair based on the column value differences.

thank you
harsha

 

[PHV]

How will you isolate the 4 real twin pairs from the 11 possible ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iamready]

what you have done is increased\decreased the values by 5% and not 0.5 that is the reason why there are 11 pairs and not 4.

 

[carp]

Here is an approximate solution that I developed on Oracle (sorry - I don't know offhand if ABS is ANSI SQL or not):

select a.*, b.*
from imready a, imready b
where a.col1 < b.col1
and abs(a.col2 - b.col2) <= .5
and abs(a.col3 - b.col3) <= .5
and abs(a.col4 - b.col4) <= .5
and abs(a.col5 - b.col5) <= .5
and abs(a.col6 - b.col6) <= .5
and abs(a.col7 - b.col7) <= .5
order by a.col2,a.col3,a.col4,a.col5,a.col6,a.col7;

Hopefully this gets you within striking distance.
This puts the twin rows onto one row - although it is not exactly the format you asked for, it gets you close.
My test results look like:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL1_1	COL2_1	COL3_1	COL4_1	COL5_1	COL6_1	COL7_1
01C05669	131.43	131.43	170.88	174.83	224.07	236.28	02C10226	131.52	131.52	170.94	174.94	224.13	236.27
02C09931	131.51	131.51	170.94	174.98	220.06	248.26	HI1330	131.59	131.59	170.94	174.95	220.25	248.44
01C07329	131.51	131.51	174.96	182.91	236.27	236.27	01C07330	131.6	131.6	175.07	183.04	236.32	236.32
01C06059	135.68	135.68	178.77	186.71	236.09	240.23	02C09873	135.8	135.8	179	186.98	236.29	240.3

 

[PHV]

OOps, misread.
It's OK now, only 4 pairs:
SELECT A.Column1, A.Col2, A.Col3, A.Col4, A.Col5, A.Col6, A.Col7
,B.Column1, B.Col2, B.Col3, B.Col4, B.Col5, B.Col6, B.Col7
FROM Table7 A, Table7 B
WHERE Abs(A.Col2 - B.Col2) <= 0.5
AND Abs(A.Col3 - B.Col3) <= 0.5
AND Abs(A.Col4 - B.Col4) <= 0.5
AND Abs(A.Col5 - B.Col5) <= 0.5
AND Abs(A.Col6 - B.Col6) <= 0.5
AND Abs(A.Col7 - B.Col7) <= 0.5
AND A.Column1<B.Column1
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iamready]

thank you guys it worked out pretty well

regards
harsha