T-SQL: Need Top N to always return N rows, even if null or blank

Question

Typically, the command

Select Top 5 * FROM ourTable

will return up to 5 rows, but less, depending whether the rows exist.

I want to ensure that it always returns 5 rows, (or in general N rows). What is the syntax to achieve this?

The idea is to sort of generalize the LINQ concept of "FirstOrDefault" to "First_N_OrDefault", but using TSQL not LINQ.

Clearly, the 'extra' rows would have null or empty columns.

This is for Microsoft SQL Server 2014 using SSMS 14.0.17

I want to use the "TOP" syntax, if at all possible, therefore it is different than the possible duplicate. Also, as noted below, this is possibly something that could be solved at a different layer in the system, but it would be nice to have for TSQL as well.

Possible duplicate of [Add empty row to query results if no results found](https://stackoverflow.com/questions/3365697/add-empty-row-to-query-results-if-no-results-found) — Sebastian Brosch, May 08 '19 at 13:06
Without an Order By clause your top 5 will be nondeterministic. — Mark Kram, May 08 '19 at 13:07
This feels like an [XY Problem](http://xyproblem.info/). Why would you need N number of completely null records? — JNevill, May 08 '19 at 13:07
@JNevil -- The rows are eventually pivoted to columns, and every column must have a value. One could solve it at a later 'layer' in the system -- so in a way, you are right. — JosephDoggie, May 08 '19 at 13:09
@Sebastian Brosch -- there is some commonality, but I am focusing on the "TOP" syntax, which is not in the OP's question in the 'possible duplicate' post. — JosephDoggie, May 08 '19 at 13:13
I see. It's like an application that sits on top that displays results and you are trying to preserve layout widths or something. It feels hacky and wrong, but if you don't have control over the codebase that is consuming this, then I can see the purpose. My concern was that maybe you were consuming into (as an example) a list object in C# and decided you had to have 5 elements in your list and then decided to force your database to conform this odd requirement. — JNevill, May 08 '19 at 13:13
@JNevill -- It appears that you are correct JNevill and it is better solved at another layer. Still, I would tend to think being able to do something like this with "Top" would be good (see discussion of answer with various scenarios that are data-dependent).... It would sort of generalize the LINQ concept of "FirstOrDefault" to "First_N_OrDefault" — JosephDoggie, May 09 '19 at 19:12

GSerg · Answer 1 · 2019-05-08T13:12:58.260

6

select top (5) c1, c2, c3 from (
  select top (5) c1, c2, c3, 0 as priority from ourTable
  union all
  select c1, c2, c3, 1 from (values (null, null, null), (null, null, null), (null, null, null), (null, null, null), (null, null, null)) v (c1, c2, c3)
) t
order by priority

edited May 08 '19 at 13:12

answered May 08 '19 at 13:07

GSerg

76,472
17
159
346

Sebastian Brosch · Accepted Answer · 2019-05-08T13:41:14.183

You can use another dummy table with rows to generate empty rows of your table with a not matching JOIN. So you don't have to repeat the columns and rows in the UNION ALL part:

SELECT TOP 5 * FROM (
   SELECT 0 AS isDummy, * FROM table_name
   -- WHERE column_name = value
   UNION ALL
   SELECT 1 AS isDummy, t1.* FROM table_name t1 
      RIGHT JOIN INFORMATION_SCHEMA.COLUMNS ON t1.id = -1000 -- not valid condition so t1 columns are empty.
) t2
ORDER BY isDummy ASC

In this case the INFORMATION_SCHEMA.COLUMNS table is used to generate the additional rows. You can choose any other table with rows. You can use a TOP N value up to the count of rows in the right table (here: INFORMATION_SCHEMA.COLUMNS).

You can also generate a table with many rows (like on a calendar table):

SELECT TOP 5 * FROM (
    SELECT 0 isDummy, * FROM table_name
    -- WHERE column_name = value
    UNION ALL
    SELECT 1 isDummy, t1.* FROM table_name t1 RIGHT JOIN (
        SELECT * FROM 
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
    ) t2 ON t1.id = -1000 -- not valid condition so t1 columns are empty.
)x
ORDER BY isDummy ASC

score 1 · Answer 3 · answered May 08 '19 at 14:07

You can use a limited tally table together with a gaplessly generated row-number like here:

The SELECT is always the same. The only thing changing is the amount of rows in the mockup-table:

DECLARE @TopCount INT=5;

--Case 1: More then 5 rows in the table

DECLARE @tbl TABLE(ID INT IDENTITY,SomeValue VARCHAR(100));
INSERT INTO @tbl VALUES
 ('Value1'),('Value2'),('Value3'),('Value4'),('Value5'),('Value6'),('Value7');

WITH Tally(Nmbr) AS(SELECT TOP(@TopCount) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
    ,NumberedRows AS(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS GeneratedRowNumber, * FROM @tbl)
SELECT *
FROM NumberedRows nr
FULL OUTER JOIN Tally t ON nr.GeneratedRowNumber=t.Nmbr;

--Case 2: Less than 5 rows in the table

DELETE FROM @tbl WHERE ID BETWEEN 2 AND 5;

WITH Tally(Nmbr) AS(SELECT TOP(@TopCount) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
    ,NumberedRows AS(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS GeneratedRowNumber, * FROM @tbl)
SELECT *
FROM NumberedRows nr
FULL OUTER JOIN Tally t ON nr.GeneratedRowNumber=t.Nmbr;

--Case 3: Exactly one row in the table

DELETE FROM @tbl WHERE ID <> 6;

WITH Tally(Nmbr) AS(SELECT TOP(@TopCount) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
    ,NumberedRows AS(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS GeneratedRowNumber, * FROM @tbl)
SELECT *
FROM NumberedRows nr
FULL OUTER JOIN Tally t ON nr.GeneratedRowNumber=t.Nmbr;

--Case 4: Table is empty

DELETE FROM @tbl;

WITH Tally(Nmbr) AS(SELECT TOP(@TopCount) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values)
    ,NumberedRows AS(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS GeneratedRowNumber, * FROM @tbl)
SELECT *
FROM NumberedRows nr
FULL OUTER JOIN Tally t ON nr.GeneratedRowNumber=t.Nmbr;

This will return all rows from the source, but at least the specified count.

If you want to limit the set to exactly 5 rows (e.g. in "Case 1"), you can use SELECT TOP(@TopCount) * and place an appropriate ORDER BY. This would return the specified row count in any case.

Good answer, but a better answer would be 'unified' for all possible situations, if this is feasible. — JosephDoggie, May 08 '19 at 16:25
@JosephDoggie, Sorry, I don't get this... The approach above would cover each and any situation... There is no *out-of-the-box-magic-let-it-ever-be-x-rows* in SQL-Server. You might use GSerg's approach and create the count of empty row-sets dynamically on string base and use `EXEC()` but that would have some major draw backs... — Shnugo, May 08 '19 at 16:30

T-SQL: Need Top N to always return N rows, even if null or blank

3 Answers3