There is a table. It is necessary to make a request to it and return at least 5 entries under certain conditions. If there are no 5 records in the table that satisfy 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 - empty lines).

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 | -----------------------------------------------------

Required query (SELECT data, str_data, status FROM table WHERE status = 1) , which returns 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 in table 5 "zero" entries (last 2 lines), but this option is not pleasant.

  • And if you make a temporary table, fill it with zeros, update those fields where you need it, and then simple select * from #temp and everything will always have the same number of rows. - namak7
  • Do you mean to manually fill in some buffer table, so that there will always be 5 records? The conditions of my task are such that all this needs to be implemented in one query and only using SQL tools. - dzukp

1 answer 1

For example:

 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
  • one
    @msi, now the question to the vehicle: why? It is better to do such tricks not on SQL. - alexlz
  • one
    I am doing a project in SCADA Trace Mode 6. In my power, only the text of the SQL query, the processing of the results is not in my power here. The developers of Trace Mode 6 have made a very inconvenient functionality for working with the database. Here it is necessary to excel in every way. - dzukp
  • @alexlz, I just answer the questions. :-) - msi