1

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 only 1 row from several rows in Table B based on closest DateTime

4
  • Are you saying that if there are multiple rows in table B matching the same ID_Fruit, you only want to return the row with the "highest" or "largest" date? Jul 23, 2014 at 8:58
  • @PhilSumner yes that's right 100% thank you Jul 23, 2014 at 9:02
  • 2
    But '2014-07-28' is later than '2014-06-25'. Do you want the latest date or the earliest date? Jul 23, 2014 at 11:48
  • That's exactly why I asked the question. Jul 23, 2014 at 21:02

2 Answers 2

3

Yes, shouldn't be too difficult, try:

SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime]
FROM TABLEA a
INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit
INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime 
      ON b.[DateTime] = maxtime.[DateTime]

Its not quite clear if you mean that it should be the most recent, but I have assumed that is what you mean.

-2

I get the suggestion of @blobbles and rewrite my response.

I divide my answer into two parts for convenience:

  1. In the case of table A contains all the idFruit of table B or when data quantity are not so big.

    WITH fruit AS ( 
        SELECT idFruit, MAX (dt) dt
        FROM B
        WHERE b.idCrane IS NOT NULL
        GROUP BY B.idFruit
    )
    SELECT *
    FROM A, B, fruit
    WHERE     a.idFruit = fruit.idFruit
              AND b.idFruit = fruit.idFruit
              AND b.dt = fruit.dt
    

    IMVHO it's a more feasible solution.

    This is the simplest case. In case of necessity it's possible add an HINT materialize in the with clause to increase performance.

  2. In the case of table A that do not contains all the idFruit of table B.

    I think the with block can reduce the resulting record set if the condition between A and B are present.

    WITH fruit AS ( 
        SELECT B.idFruit, MAX (b.dt) dt
        FROM A, B
        WHERE a.idFruit = B.idFruit 
              AND b.idCrane IS NOT NULL
        GROUP BY B.idFruit
    )
    

P.S. I leave the explicit statement between A, B and Fruit instead of JOIN just because, to me, it seems easier to read, but I agree with what he says @blobbles in the comments below.

3
  • You do have a join, three joins actually. Without a JOIN keyword. And table A in the cte is not needed at all. Your query is essentially the same as the one in @blobbles answer. Jul 24, 2014 at 9:59
  • Yes, it's right. I mean without using the JOIN word the query is similar to that of @blobbles obviously but using a with I thinks it's more nimble, nothing more.
    – LordMax
    Jul 24, 2014 at 12:02
  • Edit your answer to take out the unnecessary join in your CTE and I will probably agree with you! It will look nicer, but I recommend explicitly using a JOIN in your outer statement instead of A,B,fruit. The query optimiser will turn these into joins internally anyway, so you may as well say what they are!
    – blobbles
    Jul 24, 2014 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.