Select id with max date from SQL server

There are a few ways this could be implemented, I will give the 2 most common ways; Option 1 is to use a Top (1) / Order By method and Option 2 is to use a SubQuery with Aggregate method. Both of these will give the same correct results.

Option 1 is easier to code; however, takes a performance hit as it internally generates a cursor to perform the sorting. Option 2 may be a little harder to code, but has a much lower query cost (a unitless measure of overall cost, originally it was based on seconds to execute... in the 1990s)

I tested with your data, first with TransID as a BIGINT and then again as CHAR(10). Results were near identical, with the QueryCost of Option1/Option2 = 2.23. I also tried with TransID as a Primary Key and a Unique Clustered index on (TransID, [Date]). Query ratio changed to 2.25 to 1.

Testing Scripts

DECLARE @Rnt1 TABLE (
	TransID	CHAR(10) NOT NULL ,
	[Date]	Date NULL
)

INSERT @Rnt1
VALUES	('0000018801','2017/08/29')
,		('0000025950','2017/09/18')
,		('0000066677','2017/12/07')
,		('0000096617','2018/02/05')
,		('0000181068','2018/08/02')
,		('0000183129','2018/08/07')
,		('0000206606','2018/09/27')
,		('0000215028','2018/10/15')
,		('0000232571','2018/10/31')
,		('0000260763','2018/11/21')
,		('0000259321','2018/12/07')
,		('0000260762','2018/12/11')

-- [=====[ Option 1 ]=====]
SELECT	TOP 1 TransID, [Date] 
FROM		@RNT1 
ORDER BY	[Date] DESC

-- [=====[ Option 2 ]=====]
SELECT	TransID,[Date]
FROM		@RNT1
WHERE	[Date]= (SELECT Max([Date]) FROM @Rnt1)

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

-- [=====[ Option 1 ]=====]
SELECT	TOP 1 
        TransID, [Date] 
FROM    #RNT1 
ORDER BY [Date] DESC, 
         TransID DESC

-- [=====[ Option 2 ]=====]
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.