I want to get the last id with max date
From your question I got the impression that a single date could have multiple rows containing different values in TransId. If this is the case, then you should ensure that you really fetch the highest id value. To do this you need to add extra conditions or sorting to pick the correct value.
Modified from the queries already provided in the previous post, you could use something like
SELECT TOP 1
TransID, [Date]
FROM #RNT1
ORDER BY [Date] DESC,
TransID DESC
SELECT TOP 1
TransID, [Date]
FROM #RNT1
WHERE [Date]= (SELECT Max([Date]) FROM #RNT1)
ORDER BY TransID DESC
To add some data to the conversation about the performance, you could create some test rows:
CREATE TABLE #RNT1 (
TransID CHAR(10) NOT NULL ,
[Date] Date NULL
)
DECLARE @counter int
BEGIN
SET @counter = 1;
WHILE @counter < 10000000
BEGIN
INSERT INTO #RNT1 VALUES (RAND()*1000000, GETDATE()-RAND()*500);
SET @counter = @counter + 1;
END;
END;
If you investigate the plans, they look very different
Option 1
--------
|--Sort(TOP 1, ORDER BY:([tempdb].[dbo].[#RNT1].[Date] DESC, [tempdb].[dbo].[#RNT1].[TransID] DESC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
Option 2
--------
|--Sort(TOP 1, ORDER BY:([tempdb].[dbo].[#RNT1].[TransID] DESC))
|--Nested Loops(Inner Join, WHERE:([Expr1006]=[tempdb].[dbo].[#RNT1].[Date]))
|--Stream Aggregate(DEFINE:([Expr1006]=MAX([tempdb].[dbo].[#RNT1].[Date])))
| |--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
So what is the effect in practice, with the test cases generated above, they perform almost identically
Option 1
--------
Table '#RNT1'. Scan count 1, logical reads 28572, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1515 ms, elapsed time = 1525 ms.
Option 2
--------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#RNT1'. Scan count 2, logical reads 57144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1594 ms, elapsed time = 1593 ms.
As already pointed out by @MadMyche both statements use full table scans since no indices are present. The second query does the scan twice resulting in much higher logical IO. However this performance penalty is compensated with more efficient aggregation in joining.
But as said in the beginning the most important thing is that you get the correct results if multiple rows exist with a same date.