Return one row for each ID based on date column (SQL)

Question

The DDL creates the schema and data. I am looking for a where statement where it returns only one row for each ID and that row would be the last inserted row based on the inserteddate column.

So the result would be John, 5 and Debbie, 5

select Table1.Name, Table2.Rating
From table1 join table2 on table1.ID = table2.ID
where inserteddate = max(insertedate)

.. for each ID? It seems simple but I am having a brain block.

DDL:

CREATE TABLE [dbo].[Table1](
    [Table1ID] [int] NULL,
    [Name] [varchar](50) NULL
)

CREATE TABLE [dbo].[Table2](
    [Table2ID] [int] NULL,
    [InsertedDate] [datetime] NULL,
    [Rating] [varchar](50) NULL
)

INSERT INTO [dbo].[Table1]([Table1ID], [Name])
SELECT 1, N'John' UNION ALL
SELECT 2, N'Debbie'
INSERT INTO [dbo].[Table2]([Table2ID], [InsertedDate], [Rating])
SELECT 1, '20090101 00:00:00.000', N'6' UNION ALL
SELECT 1, '20090401 00:00:00.000', N'5' UNION ALL
SELECT 2, '20090202 00:00:00.000', N'3' UNION ALL
SELECT 2, '20090303 00:00:00.000', N'5'

Answer 1

I'm sure there's a better way, but this will do it:

CREATE TABLE #Table1(
    [Table1ID] [int] NULL,
    [Name] [varchar](50) NULL
)

CREATE TABLE #Table2(
    [Table2ID] [int] NULL,
    [InsertedDate] [datetime] NULL,
    [Rating] [varchar](50) NULL
)

INSERT INTO #Table1([Table1ID], [Name])
SELECT 1, N'John' UNION ALL
SELECT 2, N'Debbie'
INSERT INTO #Table2([Table2ID], [InsertedDate], [Rating])
SELECT 1, '20090101 00:00:00.000', N'6' UNION ALL
SELECT 1, '20090401 00:00:00.000', N'5' UNION ALL
SELECT 2, '20090202 00:00:00.000', N'3' UNION ALL
SELECT 2, '20090303 00:00:00.000', N'5'


select x.name, t2.rating
from 
(
select t1.table1id, t1.name, max(t2.inserteddate) as inserteddate
from #table1 t1
join
#table2 t2
on
t1.table1id = t2.table2id
group by t1.table1id, t1.name
) x
join
#table2 t2
on
x.table1id = t2.table2id
and 
x.inserteddate = t2.inserteddate

Answer 2

select
    table1.name,
    table2.rating
from
    table1
    inner join table2 on 
     table2.id = (select top 1 t2.id from table2 t2 where table1.id = t2.id order by t2.inserteddate desc)

oh wait, table2 doesn't have a primary key? then...

select
    table1.name,
    (select top 1 t2.rating 
    from table2 t2 
    where table1.id = t2.id order by t2.inserteddate desc) as last_rating
from
    table1

Answer 3

How about this:

SELECT 
  Table1.Name, Table2.Rating
FROM
  table1 
INNER JOIN 
  table2 ON table1.Table1ID = table2.Table2ID
WHERE
  inserteddate = (SELECT MAX(InsertedDate) 
                    FROM Table2 t2 
                   WHERE t2.Table2ID = Table2.Table2ID)

In this case, an index on the InsertedDate column would be very helpful!

Or if you're on SQL Server 2005 and up, you could also use a CTE (Common Table Expression) with a ROW_NUMBER() and a PARTITION OVER statement like this:

WITH HelperCTE AS
(
  SELECT
    Table1.Name, Table2.Rating,
    ROW_NUMBER() OVER(PARTITION BY Table1.Table1ID 
                      ORDER BY Table2.InsertedDate DESC) AS 'RowNum'
  FROM
    table1 
  INNER JOIN
    table2 ON table1.Table1ID = table2.Table2ID
)
SELECT Name, Rating FROM HelperCTE
WHERE RowNum = 1

This creates like a "temporary" view (the CTE) and numbers the entries partitioned by Table1ID (individual numbering for each separate Table1ID) and orders them descending by InsertedDate - so for each unique Table1ID, the most recent entry will have RowNum = 1.

Marc