D
D
dzukp2014-09-03 22:11:09
SQL
dzukp, 2014-09-03 22:11:09

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

2 answer(s)
D
dzukp, 2014-09-03
@dzukp

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

S
s1dney, 2014-09-04
@s1dney

Really, tin, you can't say it better.
Why do this at all? Screw those null strings on the side processing this result, why mock mysql?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question