Answer the question
In order to leave comments, you need to log in
How to create a query to MSSQL "SELECT * FROM table" that returns a fixed number of rows, even if there is no data?
There is a table. It is necessary to make a request to it and return at least 5 records under certain conditions. If there are no 5 records in the table that meet the specified conditions, it is necessary to return those records that satisfy the condition (for example, 3 records) and 2 more records with some fixed data (numeric fields 0, string fields - empty strings).
Example. table table
| INT id | INT data | VARCHAR str_data | INT status |
-------------------------------------------------- -----------
| 1 | 123 | "qwert" | 1 |
| 2 | 343 | "zzzzz" | 1 |
| 3 | 923 | "qweq" | 2 |
| 4 | 843 | "qdfgrt" | 2 |
| 5 | 763 | "qddftp" | 1 |
-------------------------------------------------- -----------
We need a query (SELECT data, str_data, status FROM table WHERE status = 1) that will return the following:
| INT data | VARCHAR str_data | INT status |
-------------------------------------------------- --
| 123 | "qwert" | 1 |
| 343 | "zzzzz" | 1 |
| 763 | "qddftp" | 1 |
| 0 | "" | 0 |
| 0 | "" | 0 |
-------------------------------------------------- --
The only thing that comes to mind is to keep 5 "null" records in the table (the last 2 rows), but I don't like this option.
Answer the question
In order to leave comments, you need to log in
If anyone is interested, here it is:
select top 5 * from (
SELECT data, str_data, status FROM table WHERE status = 1
union all
select 0, '', 0
union all
select 0, '', 0
union all
select 0, '', 0
union all
select 0, '', 0
union all
select 0, '', 0
) X
order by status desc
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question