SQL Server Top 1

Question

In Microsoft SQL Server 2005 or above, I would like to get the first row, and if there is no matching row, then return a row with default values.

SELECT TOP 1 ID,Name
FROM TableName
UNION ALL 
SELECT 0,''
ORDER BY ID DESC

This works, except that it returns two rows if there is data in the table, and 1 row if not. I'd like it to always return 1 row. I think it has something to do with EXISTS, but I'm not sure. It would be something like:

SELECT TOP 1 * FROM Contact 
WHERE EXISTS(select * from contact)

But if not EXISTS, then SELECT 0,''

Answer 1

IF EXISTS ( SELECT TOP 1 ID, Name FROM TableName )
 BEGIN
     SELECT TOP 1 ID, Name FROM TableName
 END
ELSE
 BEGIN
 --exists returned no rows
 --send a default row
 SELECT 0, ''
 END

Answer 2

Use:

  SELECT TOP 1
         x.id,
         x.name
    FROM (SELECT t.id,
                 t.name
            FROM TABLENAME t
          UNION ALL
          SELECT 0, 
                 '') x
ORDER BY id DESC

Using a CTE equivalent:

WITH query AS (
    SELECT t.id,
           t.name
      FROM TABLENAME t
    UNION ALL
    SELECT 0, 
           '')
  SELECT TOP 1
         x.id,
         x.name
    FROM query x
ORDER BY x.id DESC

Answer 3

put the top oustide of the UNION query

SELECT TOP 1 * FROM(
SELECT  ID,Name
FROM TableName
UNION ALL 
SELECT 0,''
) z
ORDER BY ID DESC

Answer 4

CREATE TABLE #sample(id INT, data VARCHAR(10))

SELECT TOP 1 id, data INTO #temp FROM #sample
IF @@ROWCOUNT = 0 INSERT INTO #temp VALUES (null, null)
SELECT * FROM #temp

Answer 5

What happens when the table is very full and you might want to specify which row of your top 1 to get, such as the first name? OMG Ponies' query will return the wrong answer in that case if you just change the ORDER BY clause. His query also costs about 8% more CPU than this modification (though it has equal reads)

SELECT TOP 1 *
FROM (
   SELECT TOP 1 ID,Name
   FROM TableName
   ORDER BY Name
   UNION ALL
   SELECT 0,''
) X
ORDER BY ID DESC

The difference is that the inner query has a TOP 1 also, and which TOP 1 can be specified there (as shown).

Just for fun, this is another way to do it which performs very closely to the above query (-15ms to +30ms). While it's more complicated than necessary for such a simple query, it demonstrates a technique that I don't see other SQL folks using very often.

SELECT
   ID = Coalesce(T.ID, 0),
   Name = Coalesce(T.Name, '')
FROM
   (SELECT 1) X (Num)
   LEFT JOIN (
      SELECT TOP 1 ID, Name
      FROM TableName
      ORDER BY ID DESC
   ) T ON 1 = 1 -- effective cross join but does not limit rows in the first table