Get the last row inserted using the CreateDate field
I am trying to get the last record inserted into a SQL table based on its CreateDate. For example, 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'm wondering if there is a better way to achieve the same result. Below is the table, data and query that I am using right now for my test. Is there a 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)
Thank!
+1
source to share
4 answers
I love this outer join solution - it didn't happen to me. But Display Estimated Execution Plan found it to be more expensive than I usually solve this problem. (May be related to indexes on my table - your mileage may vary) Try:
SELECT
T1.ColumnA,
T1.ColumnB,
T1.ColumnC,
T1.CreatedDate
FROM
dbo.TestTable T1
JOIN (SELECT ColumnA,
ColumnB,
max(CreatedDate) MaxDate
FROM
dbo.TestTable
GROUP BY ColumnA,
ColumnB) T2
ON T1.ColumnA = T2.ColumnA
AND T1.ColumnB = T2.ColumnB
AND T1.CreatedDate = T2.MaxDate
+1
source to share
There are various ways to skin this cat. Some that have not yet been published:
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
0
source to share