is this a candidate for a sql groupby query?

Question

I have two tables: TableA (ID [int, pk], Name [string]) and TableB (ID [int, pk], TableA_ID [int, fk], Name [string], DateStamp [datetime (dd/mm/yyyy hh:MM:ss)]). There is a one-to-many relationship between TableA and TableB.

An inner join on the two tables will give me the following results:

TableA.ID, TableA.Name, TableB.Name, TableB.DateStamp
        1,    'File A', 'Version 1', 01/01/2009 15:00:00
        1,    'File A', 'Version 2', 05/01/2009 08:15:00
        1,    'File A', 'Version 3', 06/01/2009 19:33:00
        2,    'File B', 'Version 1', 03/01/2009 09:10:00
        2,    'File B', 'Version 2', 20/01/2009 20:00:00
        3,    'File C', 'Version 1', 01/01/2009 17:00:00

What I actually want is the following (each row from TableA and the last matching row from TableB):

TableA.ID, TableA.Name, TableB.Name, TableB.DateStamp
        1,    'File A', 'Version 3', 06/01/2009 19:33:00
        2,    'File B', 'Version 2', 20/01/2009 20:00:00
        3,    'File C', 'Version 1', 01/01/2009 17:00:00

This is the query that I'm using to achieve this:

SELECT ta.ID, ta.Name, tb.Name, tb.DateStamp
FROM TableA ta INNER JOIN TableB tb ON ta.ID = tb.TableA_ID
WHERE tb.ID IN (
 SELECT TOP 1 tb2.ID 
 FROM TableB tb2 
 WHERE tb2.TableA_ID = ta.ID 
 ORDER BY tb2.DateStamp DESC)

That works but my gut feeling is that I'm not doing this in the "best way". It looks like it is a candidate for an aggregation query (i.e. groupby) but I didn't have any luck with that. In the end I always had to use a subquery to get the row I'm after in TableB.

Any help much appreciated.

Answer 1

if you want to use group by you can use:

select 
    ta.id, ta.name, tb.name, tb.dateStamp
from 
    tableA ta 
    inner join tableB tb on ta.id = tb.tablea_id
    inner join (
     select tablea_id, max(DateStamp) as maxDateStamp from tableB
     group by tablea_id
    ) latestB 
     on tb.tablea_id = latestB.tablea_id
     and tb.DateStamp = latestB.maxDateStamp

But i will return multiple records if you have multiple entries in tableB with the same value of DateStamp referencing same row in tableA

Answer 2

No, there is no requirement to do GROUP BY here, this should be solved through a correlated sub-query:

SELECT
  TableA.ID, 
  TableA.Name, 
  TableB.Name, 
  TableB.DateStamp
FROM
  TableA
  INNER JOIN TableB ON 
    TableA.ID = TableB.TableA_ID
    AND TableB.DateStamp = (
      SELECT MAX(DateStamp) 
      FROM TableB
      WHERE TableA_ID = TableA.ID
    )

An additional GROUP BY is only necessary if you have more than one record in TableB with equal TableA_ID and equal DateStamp.

---

For the specific example you've shown, a GROUP BY query happens to produce the correct result. It's still wrong, because the correct result is more a side-effect in this situation.

SELECT
  TableA.ID, 
  TableA.Name, 
  MAX(TableB.Name) Max_TableBName, 
  MAX(TableB.DateStamp) Max_TableBDateStamp
FROM
  TableA
  INNER JOIN TableB ON TableA.ID = TableB.TableA_ID
GROUP BY
  TableA.ID, 
  TableA.Name

This relies on the coincidence that MAX(TableB.Name) is in fact the value you want to get out, and it is aligned with MAX(TableB.DateStamp). But since this correlation is a mere accident, the GROUP BY query is wrong.

Answer 3

You can't reliably get more than one field from the B table in a grouping, but you can join the B table against the result to get the other fields:

select x.ID, x.Name, b.Name, b.DateStamp
from (
  select a.ID, a.Name, max(b.DateStamp) as DateStamp
  from TableA a
  inner join TableB b on b.TableA_ID = a.ID
  group by a.ID, a.Name
) x
inner join TableB b on b.TableA_ID = x.ID and b.DateStamp = x.DateStamp

Answer 4

You can also make a query using analytical functions. In Oracle you can do:

select distinct
       A.Id
,      A.Name
,      first_value(B.Name)      over (partition by B.id
                                      order     by B.DateStamp desc)   BName
,      first_value(B.DateStamp) over (partition by B.id
                                      order     by B.DateStamp desc)   DateStamp
from   TableA A inner join TableB B  on A.id = B.id

Answer 5

You can also try RANK() OVER function:

-- Test data
DECLARE @TableA TABLE (ID INT, Name VARCHAR(20))
INSERT INTO @TableA
SELECT 1, 'File A' UNION
SELECT 2, 'File B' UNION
SELECT 3, 'File C'

DECLARE @TableB TABLE (ID INT, TableAID INT, Name VARCHAR(20), 
  DateStamp DATETIME)
INSERT INTO @TableB
SELECT 1, 1, 'Version 1', '01/01/2009 15:00:00' UNION
SELECT 2, 1, 'Version 2', '01/05/2009 08:15:00' UNION
SELECT 3, 1, 'Version 3', '01/06/2009 19:33:00' UNION
SELECT 4, 2, 'Version 1', '01/03/2009 09:10:00' UNION
SELECT 5, 2, 'Version 2', '01/20/2009 20:00:00' UNION
SELECT 6, 3, 'Version 1', '01/01/2009 17:00:00'

-- Actually answer
SELECT M.ID, M.AName, M.BName, M.DateStamp FROM
(   SELECT RANK() OVER(PARTITION BY A.ID ORDER BY B.DateStamp DESC) AS N, 
    A.ID, A.Name AS AName, B.Name AS BName, B.DateStamp
    FROM @TableA A INNER JOIN @TableB B ON A.ID = B.TableAID
) M WHERE M.N = 1

See 2. Last Date selection with grouping - using RANK() OVER