CODEWITHSUNDEEP
sqlsql-serverselectrows
Luis Angel
Luis Angel

Reputation: 13

SQL select returning a defined number of rows

I want to get a 14 rows query result even when table has only 6 records available.

For example table has only 6 records so I want to make a SELECT and the output throw me the 6 records with 8 blank rows.

Like this:

|trackings    |
---------------
|track1       |
|track2       |
|track3       |
|track4       |
|track5       |
|track6       |
|   *blank*   |
|   *blank*   |
|   *blank*   |
|   *blank*   |
|   *blank*   |
|   *blank*   |
|   *blank*   |
|   *blank*   |

I search on google how to achieve this but I couldn't find the best tags to find it, I read some examples with UNION but in this case is more than 1 blank or custom row.

Note that the records in the table may change but I just need 14 rows. These can be the 14 rows with data or some of them in blank depending the case.

Thanks and sorry for my english!

Upvotes: 1

Views: 834

Answers (3)

Alex
Alex

Reputation: 21766

You might be better off implementing this in your application layer, however, if you want to do this in SQL, you can use a CTE to implement this. The first CTE creates 14 empty records, in the second CTE these empty records are unioned with the results of your query, and in the final query the top 14 results are selected sorting the non-empty records at the top:

WITH cte
    AS ( SELECT 0 AS Id ,
                ' ' AS EmptyData
        UNION ALL
        SELECT  Id + 1 AS Id ,
                EmptyData
        FROM    cte
        WHERE   Id < 14
       ),
   cte2
    AS ( SELECT 1 AS SortOrder ,
                trackings
        FROM    dbo.data
        UNION ALL
        SELECT  2 AS SortOrder ,
                EmptyData
        FROM    cte
       )
SELECT TOP 14
      trackings
FROM  cte2
ORDER BY SortOrder

The advantage of this approach is that you can easily change the total number of records, just replace the two occurrences of 14 with a different number.

Upvotes: 1

Saic Siquot
Saic Siquot

Reputation: 6513

I have added otherField1, otherField2 just to do a generic answer
This resultset, allways have 14 records, the latests filled with null if there are less that that number in the table

select top 14 tracking, otherField1, otherField2
from (
    select tracking, otherField1, otherField2, 1 as orderBy from yourTable
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2
    union all select null, null, null, 2  -- this is 14 times here
) as subQuery
order by orderBy, tracking

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271013

If you don't care if the rows are blank, you can generate the rows. Here is a method using outer apply:

with t as (
      select t.*, row_number() over (select null) as seqnum
      from t
     )
select t.*
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14)
     ) n(n) left join
     t
     on t.n = n.n;

You can use a numbers table or subquery to generate the numbers as well.

Upvotes: 2

Related Questions