CODEWITHSUNDEEP
sql-servert-sql
tony95
tony95

Reputation: 108

Join data from one table between ordered values in another table

I have two table of numbers. I need to join them sequentially. I am looking for the most efficient way to do this. Actually. I am working with dates but I will explain with numbers for simplicity.

TABLE 1 has data 1, 3, 6, 8
TABLE 2 has data 1.1, 2.3, 4.5, 6.5, 8.8, 8.9

I want values >= 1 and < 3 ... >= 3 and < 6 ... >=6 < 8 etc

I want a result like

1 1.1    
1 2.3    
3 4.5    
6 6.5    
8 8.8    
8 8.9

So, basically I want the values >= my current row but < then next higher row.

I need this to be very efficient because it will be run on a large dataset using dates instead of numbers.

Upvotes: 2

Views: 43

Answers (3)

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

good performance here:

CREATE TABLE t1(data int)
CREATE TABLE t2(data numeric(18,2))



INSERT INTO t1
VALUES(1), (3),(6), (8)



INSERT INTO t2 
VALUES (1.1), (2.3), (4.5), (6.5), (8.8), (8.9)



WITH CTE AS 
(SELECT ROW_NUMBER() OVER (ORDER BY data) AS rn, data FROM t1)



SELECT a.data,z.data
FROM CTE a
LEFT JOIN CTE b ON a.rn+1 = b.rn
CROSS APPLY (SELECT * FROM t2 x 
             WHERE x.data >= a.data AND x.data < ISNULL(b.data,999999999)) z

Upvotes: 1

JohnS
JohnS

Reputation: 2052

This will work but needs testing for performance:

SELECT
  T1.DATA,
  T2.DATA
FROM
  Table1 T1
  JOIN Table2 T2 ON T2.Data >= T1.DATA AND
                    T2.Data < ISNULL(
                                     (
                                       SELECT TOP 1
                                        TT1.Data
                                        FROM
                                        Table1 TT1
                                       WHERE
                                        TT1.Data > T1.Data
                                       ORDER BY
                                        TT1.DATA
                                      ), T2.Data + 1)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Try this.

create TABLE #table1 (data int)
insert  #table1 
values(1), (3),( 6), (8 )

create TABLE #table2 (data numeric(6,2))
insert  #table2 values
(1.1), (2.3), (4.5), (6.5), (8.8), (8.9)


SELECT Max(col1)col1,
       col2
FROM   (SELECT CASE
                 WHEN a.data < b.data THEN a.data
               END    col1,
               b.data col2
        FROM   #table1 a
               CROSS JOIN #table2 b) a
WHERE  col1 IS NOT NULL
GROUP  BY col2

OUTPUT

+-----+-----+
|col1 | col2|
+-----+-----+
|1    | 1.10|
|1    | 2.30|
|3    | 4.50|
|6    | 6.50|
|8    | 8.80|
|8    | 8.90|
+-----+-----+

Upvotes: 2

Related Questions