CODEWITHSUNDEEP
sqljoininner-join
Declan Carroll
Declan Carroll

Reputation: 259

SQL inner join using multiple in statements on single table

Having a bit of trouble with an SQL query I am trying to create. The table format is as follows,

ID | Data Identifier | Date Added | Data Column
 1 |            1001 |      15400 | Newest Value
 1 |            1001 |      15000 | Oldest Value
 1 |            1001 |      15200 | Older Value
 1 |            1002 |      16000 | Newest Value
 2 |            1001 |      16000 | Newest Value

What I am trying to do is, for each ID in a list (1,2) , and for each Data Identifier id in (1001,1002) return just the rows with the first matching field id and date nearest and below 16001.

So the results would be :

1 | 1001 | 15400 | Newest Value
1 | 1002 | 16000 | Newest Value
2 | 1001 | 16000 | Newest Value

I have tried several manner of joins but I keep returning duplicate records. Any advice or help would be appreciated.

Upvotes: 1

Views: 139

Answers (3)

Doro
Doro

Reputation: 671

I think in this case self-join would be the best, but I still don't get the nearest and below value... (may be 15400)

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 8991

You need to create a primary key column on your table that will not be used as an aggregate. Then you can create a CTE to select the rows required and then use it to select the data.

The aggregate function MIN(ABS(15500 - DateAdded)) will return the closest value to 15500.

WITH g AS
(
    SELECT MAX(UniqueKey) AS UniqueKey, ID, DataIdentifier, MIN(ABS(15500 - DateAdded)) AS "DateTest"
    FROM test
    GROUP BY ID, DataIdentifier
)
SELECT test.ID, test.DataIdentifier, test.DateAdded, test.DataColumn
FROM g
INNER JOIN test
    ON g.UniqueKey = test.UniqueKey

EDIT:

Screenshot of working example:

enter image description here

Upvotes: 2

Schalk
Schalk

Reputation: 142

It seems as if you want to GROUP BY and maybe a self join onto the table.

I have the following code for you:

-- Preparing a test table
INSERT INTO #tmpTable(ID, Identifier, DateAdded, DataColumn)
SELECT 1, 1001, 15400, 'Newest Value'
UNION
SELECT 1, 1001, 15000, 'Oldest Value'
UNION
SELECT  1, 1001, 15200, 'Older Value'
UNION
SELECT  1, 1002, 16000, 'Newest Value'
UNION
SELECT  2, 1001, 16000, 'Newest Value'

-- Actual Select
SELECT b.ID, b.Identifier, b.DateAdded, DataColumn
FROM 
    (SELECT ID, Identifier, MAX(DateAdded) AS DateAdded
   FROM #tmpTable
   WHERE DateAdded < 16001
   GROUP BY ID, Identifier) a
INNER JOIN #tmpTable b ON a.DateAdded = b.DateAdded
AND a.ID = b.ID
AND a.Identifier = b.Identifier

Upvotes: 2

Related Questions