CODEWITHSUNDEEP
sqlsql-server-2012
Mathematics
Mathematics

Reputation: 7618

Inner Join or what else?

What I have

Table A                                 Table B


ID    ID_Fruit    ,,, so on             ID      ID_Fruit   ID_CRATE  DateTime                
1     1002                              i_1     1002          1      2014-07-28 00:00.000
2     4534                              i_2     1002          2      2014-06-25 00:00.000
3     1243                              i_3     1243          NULL   2014-07-09 00:00.000
4     1003                              i_4     4534          1      2014-07-23 00:00.000

Result I want

ID    ID_Fruit    ,,, so on             ID      ID_Fruit    ID_CRATE     DateTime
1     1002                              i_1     1002           2      2014-06-25 00:00.000
2     4534                              i_4     4534           1      2014-07-23 00:00.000

What I am trying,

SELECT * FROM TABLEA
OUTER APPLY  
        (SELECT * FROM TableB WHERE TableB.ID_Fruit = TableA.ID_Fruit AND TableB.ID_CRATE IS NOT NULL) As Table_B

I want the only 1 row from several rows Table B based on closet DateTime

Upvotes: 0

Views: 59

Answers (4)

David Khuu
David Khuu

Reputation: 967

SELECT *
FROM TABLEA
CROSS APPLY
(
    SELECT TOP 1 *
    FROM TableB
    WHERE TableB.ID_Fruit = TableA.ID_Fruit
    AND TableB.ID_CRATE IS NOT NULL
    ORDER BY [DateTime]
) As Table_B

You were on the right track by using OUTER APPLY, but you will get all the rows from the left table, even the one with no matching row from the right table, so you may want to use CROSS APPLY instead (left rows with no matching row from right table are not returned).

You simply forgot to use TOP 1 and ORDER BY.

Here is a nice article about TOP N rows per group : http://sqlmag.com/database-development/optimizing-top-n-group-queries

Upvotes: 0

Tobsey
Tobsey

Reputation: 3400

SELECT
    TableAID,
    TableAID_Fruit,
    TableBID,
    TableBID_Fruit,
    ID_CRATE,
    DateTime
FROM
    (SELECT 
        TableA.ID TableAID,
        TableA.ID_Fruit TableAID_Fruit,
        TableB.ID TableBID,
        TableB.ID_Fruit TableBID_Fruit,
        TableB.ID_CRATE,
        TableB.DateTime,
        ROW_NUMBER() OVER (PARTITION BY TableB.ID_Fruit ORDER BY TableB.DateTime DESC) TableBOrder
    FROM
        TableA
        INNER JOIN TableB ON TableA.ID_Fruit = TableB.ID_Fruit
    ) Results
WHERE
    TableBOrder = 1

Upvotes: 0

Abend
Abend

Reputation: 589

SELECT A.*, B.* FROM TABLE_A A, TABLE_B B
WHERE A.ID_Fruit = B.ID_Fruit
and B.DateTime IN (SELECT MAX(DateTime) from TABLE_B WHERE ID_Fruit = B.ID_Fruit)

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

Write as

;with CTE as 
( Select ID,
         ID_Fruit,
         ID_CRATE,
         [DateTime],
         Row_number() over ( partition by ID_Fruit order by [DateTime]  desc) as rownum
         from Table_B
 ) 
 select A.ID,A.ID_Fruit,C.ID,C.ID_CRATE,C.[DateTime] 
 from CTE C
 join Table_A A on A.ID_Fruit = C.ID_Fruit
 where C.rownum = 1 and isnull(ID_CRATE,'') <> ''

Check Demo here..

Upvotes: 3

Related Questions