compare data within a column

[smoker616]

Ok first of i would like to do this with out a temporary table but if thats the only way then i'll survive. I have single column table of integers. I want to compare one part of the column with a different part and return every match that it has in common.

So lets say i have the column
1
3
6
9
12
2
5
6
8
9
13
6
9
10
ect.

Now for simplicitys sake lets say i want to compare the ranges 0-4 and 5-10
which would be the number sets 1,3,6,9,12 and 2,5,6,8,9,13
my desired result is 6,9 . Also im not quite sure how to use the row numbers of the integers to retrive sets of data. Is LIMIT the only way. These strings of data can be up to 10,000 integers so just reading it into php and parsing would be too costly.

 

[markVII]

Try using self-join, maybe adding a column for additional selectivity with precalculated values.

 

[tlbroadbent]

Does the table have another column or columns?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

Try this

select C.number from (select top 5 * from a) as C inner join (select top 10 * from a) as B on C.number = B.number
group by c.number having count(*) >1

 

[smoker616]

Ok let me start with what i have so far, I am using a self join but the way i am accomplishing this is innefficient in both space and operation time.
I generate sql code with php
'SELECT * FROM post_table t1,post_table t2
WHERE t1.post = t2.post AND
t1.index <='.$max1.' AND
t1.index >= '.$min1.' AND
t2.index <='.$max2.' AND
t2.index >='.$min2;
the variables for the sample table i gave would be as follows:
$min1=0 $max1=4 min2=5 max2=10
The min and the max value are in a precaculated table.

My main problems with this are that i have to go by an index to retrive the values and that i dont know how to check three columns against eachother efficently.
t1.post=t2.post=t3.post doesnt work.

I dont know exactly how to figure the code but if sql could read my mind i would write it like this.

SELECT * FROM table t1,table t2, table t3
(t1 LIMIT 5,10) JOIN ON
(t2 LIMIT 25,75) WHERE t1.num=t2.num JOIN ON
(t3 LIMIT 193,244) WHERE t2.num=t3.num;

I know that this doesnt work/make much sense, but its the best i can come up with.

 

[tlbroadbent]

You've managed to almost totally confuse me. I don't know the names of the table columns or if a column named index actually exists on the table. If there is a column, that is essentially a row count, then the following will provide the answer you seek.

SELECT *
FROM
(Select [Index], Post From table
Where [Index] Between @min1 And @max1) t1
Inner Join
(Select [Index], Post From table
Where [Index] Between @min2 And @max2) t2
On t1.Post = t2.Post
Inner Join
(Select [Index], Post From table
Where [Index] Between @min3 And @max3) t3
On t1.Post = t3.Post

If the table doesn't have a &quot;row number&quot; column then additional coding will be required. However, it is essential to have a unique identifier for each row or a column that indicates row order. Remember that SQL tables are unordered by definition so we can't arbitrarily choose rows 1-5. If you can tell us the exact schema and column names, we may be able to provide the help you need.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[smoker616]

there are two tables. The first has precaculated ranges in 2 columns min and max. These ranges are used for selects on the second table. The second table has 2 columns index and post. index being the unique identifier and post being the numerical data. The example you gave me pretty much answers my question. I did want to avoid using a unique identifier but apparently in sql that is impossible. I was expecting a sql function that would do somthing like
TABLE_MIN = TABLE_PTR + (min * ROWSIZE)
TABLE_MAX = TABLE_PTR + (max * ROWSIZE)
If this was C and TABLE_PTR was the adress of the start of the table and row size was the size of each row of the table. TABLE_MIN and TABLE_MAX would be pointers to the start and the end of the data that i wanted to retrieve.
I guess sql works in a non sequential way.

 

[smoker616]

Also I think my host uses mysql 4.0.1 which is a version before subquery support is there a way to do it without subquerys?

 

[tlbroadbent]

If you are using MySQl, then you should be posting questions in the MySQL forum. The answers you get in this forum may not work in MySQL.

SQL Server doesn't have a row pointer or row number function. I don't know if that functionality exists in MySQL.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[smoker616]

Sorry for posting in the wrong forum. I figured a way to do it with out subquerys.
SELECT * FROM post_table t1
INNER JOIN post_table t2 ON (t1.post=t2.post)
INNER JOIN post_table t3 ON (t2.post=t3.post)
WHERE t1.index BETWEEN 294 AND 1318
AND t2.index BETWEEN 2210 AND 3379
AND t3.index BETWEEN 30122 AND 30237

This runs very slow though im trying to find a more efficent way to do this.

 

[tlbroadbent]

You'll want to make sure the table has a compound index on the Index and Post columns, in that order. At least, that is waht I would do in MS SQL Server.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[smoker616]

Ok i am very close to resolving this, I did an EXPLAIN on the query above and found that the index was only applying to 1 column which is why my join speed was so slow. The index is on the left-side of the table and is set to primary. I changed the above query to do RIGHT JOINS instead of INNER and the index then applied to t2 and t3 but did not apply to t1. Do you know how I can have the index apply to all three.

 

[smoker616]

Never mind i was mistaken. INNER JOINS work fine The post i have above uses indexs efficently.