Get the latest row inserted with the help of CreatedDate field

Question

Hello all,

I am trying to get the latest record inserted in a SQL table based on its CreatedDate. For instance, in the table below, I would like to get the third row.

A   B C 2009-05-04 19:30:52.847
A   B D 2009-05-04 19:30:55.050
A   B E 2009-05-04 19:30:57.003

I have a working query, but I am wondering if there is better way to achieve the same result. Below is the table, data and query I am using right now for my test. Is there any better way to do this?

CREATE TABLE TestTable (
  ColumnA     NVARCHAR(10),
  ColumnB     NVARCHAR(10),
  ColumnC     NVARCHAR(10),
  CreatedDate DATETIME    DEFAULT Getutcdate())

INSERT INTO TestTable(ColumnA, ColumnB, ColumnC) VALUES ('A', 'B', 'C');
INSERT INTO TestTable(ColumnA, ColumnB, ColumnC) VALUES ('A', 'B', 'D');
INSERT INTO TestTable(ColumnA, ColumnB, ColumnC) VALUES ('A', 'B', 'E');

SELECT *
FROM   TestTable
WHERE  CreatedDate = (SELECT   Max(CreatedDate)
                      FROM     TestTable
                      WHERE    ColumnA = 'A'
                               AND ColumnB = 'B'
                      GROUP BY ColumnA,
                               ColumnB)

Thanks!

Answer 1

you can also write

SELECT top 1 *
FROM   TestTable
order by CreatedDate desc

Answer 2

There are various ways to skin this cat. Some that haven't been posted yet:

SELECT
     T1.column_a,
     T1.column_b,
     T1.column_c,
     T1.created_date
FROM
     dbo.Test_Table T1
WHERE
     T1.column_a = 'A' AND
     T1.column_b = 'B' AND
     NOT EXISTS
     (
          SELECT
               *
          FROM
               dbo.Test_Table T2
          WHERE
               T2.column_a = T1.column_a AND
               T2.column_b = T1.column_b AND
               T2.created_date > T1.created_date
     )

or

SELECT
     T1.column_a,
     T1.column_b,
     T1.column_c,
     T1.created_date
FROM
     dbo.Test_Table T1
LEFT OUTER JOIN dbo.Test_Table T2 ON
     T2.column_a = T1.column_a AND
     T2.column_b = T1.column_b AND
     T2.created_date > T1.created_date
WHERE
     T1.column_a = 'A' AND
     T1.column_b = 'B' AND
     T2.column_a IS NULL