Search code examples
t-sqlselectlimit

TSQL show only first row

I have the following TSQL query: 

SELECT DISTINCT MyTable1.Date
  FROM MyTable1 
INNER JOIN MyTable2 
ON MyTable1.Id = MyTable2.Id
WHERE Name = 'John' ORDER BY MyTable1.Date DESC

It retrieves a long list of Dates, but I only need the first one, the one in the first row.

How can I get it?

Thanks a ton!

Solution

  • In SQL Server you can use TOP:

    SELECT TOP 1 MyTable1.Date
FROM MyTable1 
INNER JOIN MyTable2 
  ON MyTable1.Id = MyTable2.Id
WHERE Name = 'John' 
ORDER BY MyTable1.Date DESC

    If you need to use DISTINCT, then you can use:

    SELECT TOP 1 x.Date
FROM
(
   SELECT DISTINCT MyTable1.Date
   FROM MyTable1 
   INNER JOIN MyTable2 
     ON MyTable1.Id = MyTable2.Id
    WHERE Name = 'John' 
) x
ORDER BY x.Date DESC

    Or even:

    SELECT MAX(MyTable1.Date)
FROM MyTable1 
INNER JOIN MyTable2 
  ON MyTable1.Id = MyTable2.Id
WHERE Name = 'John' 
--ORDER BY MyTable1.Date DESC