How to extract info based on the latest row

I hv a table like this:-

Table A

SKU   Code     Bin
--------------------
FA25  A1707    Q890

Table B

SeqNo               ID              SKU     Code    Value
20110321001100909   GRADE           FA20    A1707   3
20110321001100909   SIZE            FA20    A1707   25
20110321001100909   COLOR           FA20    A1707   BLACK
20110321001100909   FINISH          FA20    A1707   GF
20110321001101161   GRADE           FA25    A1707   3
20110321001101161   SIZE            FA25    A1707   25
20110321001101161   COLOR           FA25    A1707   BLACK
20110321001101161   FINISH          FA25    A1707   GF
20110817002647812   GRADE           FA20    A1707   1
20110817002647812   SIZE            FA20    A1707   25
20110817002647812   COLOR           FA20    A1707   BLACK
20110817002647812   FINISH          FA20    A1707   AF
20110817002652517   GRADE           FA25    A1707   1
20110817002652517   SIZE            FA25    A1707   25
20110817002652517   COLOR           FA25    A1707   BLACK
20110817002652517   FINISH          FA25    A1707   AF

I use this SQL to extract the latest info:-

select a.SKU, a.CODE, a.BIN b.GRADE_1, b.SIZE_1, b.COLOR, b.FINISH
  from TableA as a
left join (select SKU, CODE,
                  MAX (CASE WHEN ID = 'GRADE' THEN VALUE ELSE NULL END) AS GRADE_1,
                  MAX (CASE WHEN ID = 'SIZE' THEN VALUE ELSE NULL END) AS SIZE_1,
                  MAX (CASE WHEN ID = 'COLOR' THEN VALUE ELSE NULL END) AS COLOR,
                  MAX (CASE WHEN ID = 'FINISH' THEN VALUE ELSE NULL END) AS FINISH
             from TableB 
         GROUP by SKU, CODE) as b ON a.SKU = b.SKU and a.CODE = b.CODE
    where a.CODE = 'A1707'

The output from the above statement, which is incorrect, is as follows:-

SKU   CODE   BIN   GRADE  SIZE  COLOR  FINISH
----------------------------------------------
FA25  A1707  Q890  3      25    BLACK  GF

The expected output shd be like this (Grade is 1 & Finish is AF):-

SKU   CODE   BIN   GRADE  SIZE  COLOR  FINISH
----------------------------------------------
FA25  A1707  Q890  1      25    BLACK  AF

How can I achieve that ?

select a.SKU, a.CODE, a.BIN, b.GRADE, b.SIZE, b.COLOR, b.FINISH 
           /*remove this line if you don't want SeqNo returned */,b.SeqNo
from #TableA a
left outer join
(
      select x.SeqNo, 
          x.SKU as SKU, 
          x.CODE as CODE,
          max(CASE WHEN ID = 'GRADE' THEN [Value] END) as GRADE,
          max(CASE WHEN ID = 'SIZE' THEN [Value] END) as SIZE,
          max(CASE WHEN ID = 'COLOR' THEN [Value] END) as COLOR,
          max(CASE WHEN ID = 'FINISH' THEN [Value] END) as FINISH
      from #TableB x
      inner join
      (
        select SKU, CODE, max(seqno) as SeqNo
        from #TableB
        group by SKU, CODE
      )y on (x.SeqNo = y.SeqNo)
      group by x.SeqNo, x.SKU, x.CODE
)b on (a.SKU = b.SKU AND a.CODE = b.CODE)

This:

SELECT a.SKU, a.Code, a.Bin 
     , b.SeqNo, b.ID, b.Value
FROM TableA AS a
  JOIN TableB AS b
    ON  b.SKU   = a.SKU
    AND b.Code  = a.Code
    AND b.SeqNo = ( SELECT MAX(bm.SeqNo)
                    FROM TableB AS bm
                    WHERE bm.SKU = a.SKU
                      AND bm.Code = a.Code
                  )

will show:

SKU   Code   Bin   SeqNo               ID        Value
-------------------------------------------------------
FA25  A1707  Q890  20110817002652517   GRADE     1
FA25  A1707  Q890  20110817002652517   SIZE      25
FA25  A1707  Q890  20110817002652517   COLOR     BLACK
FA25  A1707  Q890  20110817002652517   FINISH    AF

You can then alter the above to have what you want (search for PIVOTING/UNPIVOTING for other ways to achive same thing)

SELECT a.SKU, a.Code, a.Bin 
     , MIN( CASE WHEN b.ID = 'GRADE'
                    THEN b.Value
                    ELSE NULL
            END
          ) AS Grade 
     , MIN( CASE WHEN b.ID = 'SIZE'
                    THEN b.Value
                    ELSE NULL
            END
          ) AS Size
     , MIN( CASE WHEN b.ID = 'COLOR'
                    THEN b.Value
                    ELSE NULL
            END
          ) AS Color
     , MIN( CASE WHEN b.ID = 'FINISH'
                    THEN b.Value
                    ELSE NULL
            END
          ) AS Finish
FROM TableA AS a
  JOIN TableB AS b
    ON  b.SKU   = a.SKU
    AND b.Code  = a.Code
    AND b.SeqNo = ( SELECT MAX(bm.SeqNo)
                    FROM TableB AS bm
                    WHERE bm.SKU = a.SKU
                      AND bm.Code = a.Code
                  )
GROUP BY a.SKU, a.Code, a.Bin

will show:

SKU   Code   Bin   Grade  Size  Color  Finish
----------------------------------------------
FA25  A1707  Q890  1      25    BLACK  AF

Another way would be:

SELECT ad.SKU, ad.Code, ad.Bin 
     , b1.Value AS Grade 
     , b2.Value AS Size
     , b3.Value AS Color
     , b4.Value AS Finish
FROM
  ( SELECT a.SKU, a.Code, a.Bin
         , ( SELECT MAX(bm.SeqNo)
             FROM TableB AS bm
             WHERE bm.SKU = a.SKU
             AND bm.Code = a.Code
           ) AS MaxSeqNo 
    FROM TableA AS a
  ) AS ad
  LEFT JOIN TableB AS b1
    ON  b1.SKU   = ad.SKU
    AND b1.Code  = ad.Code
    AND b1.SeqNo = ad.MaxSeqNo
    AND b1.ID = 'GRADE'
  LEFT JOIN TableB AS b2
    ON  b2.SKU   = ad.SKU
    AND b2.Code  = ad.Code
    AND b2.SeqNo = ad.MaxSeqNo
    AND b2.ID = 'SIZE'
  LEFT JOIN TableB AS b3
    ON  b3.SKU   = ad.SKU
    AND b3.Code  = ad.Code
    AND b3.SeqNo = ad.MaxSeqNo
    AND b3.ID = 'COLOR'
  LEFT JOIN TableB AS b4
    ON  b4.SKU   = ad.SKU
    AND b4.Code  = ad.Code
    AND b4.SeqNo = ad.MaxSeqNo
    AND b4.ID = 'FINISH'