The question is the following, there are some number of scripts, they write the results of their work in the database. This query simply selects the last 10 records sorted by date, after which PHP builds a table with the latest records based on the response received, but all the scripts are mixed there.

SELECT TOP 10 [name] ,[description] ,[date] ,[status] FROM [script_monitoring].[dbo].[status] order by DATE desc

How to improve this query so that the answer is of the same kind, only for each script individually? It would be possible to organize a cycle with requests for each script name separately, but instead of 1 request, using 10 seems to me stupid. Can temporary tables help in this issue? It is important that this is a complete request.

  • one
    The answer to your question in the form in which you originally asked it can be only one! "Write a query for each table to take the last 10 records sorted by date." Want specifics - give us specifics, what tables, what scripts and what does not work? - JVic
  • The first 10 in mssql is written so select top 10 sort the order by поле . More exhaustive information on this issue can not be designated. - nick_n_a February
  • so that the answer was of the same type, only for each script individually? Specify. Need for each script 10 recent records? For everyone - the last entry? For the last 10 scripts by last entry? something else? But in any case, look towards ROW_NUMBER() OVER (PARTITION BY [script_id] ORDER BY [date] DESC) . - Akina
  • @Akina Need for each script the last 10 entries. - N. Turshiev

2 answers 2

It is necessary for each script 10 recent entries.

 SELECT t1.[name] , t1.[description] , t1.[date] , t1.[status] FROM ( SELECT t0.[name] , t0.[description] , t0.[date] , t0.[status] , ROW_NUMBER() OVER (PARTITION BY t0.[name] ORDER BY t0.[date] DESC) rownum FROM table t0 ) t1 WHERE rownum <= 10
  • tell me what does t1 and t0 mean? - N. Turshiev
  • Aliases is. Aliases in our case if. - Akina
  • Is it a variable or something? - N. Turshiev

In relational databases, there is no concept of the last N records. Sorting is also optional. You can do something like:
select ... from ... where "table name". "field with date"> (sysdate - "how much you need to look into the past") or enter into the table some kind of key id which after each insert makes id ++

  • In MS SQL is. In other DBMS, too, often comes in the form of LIMIT . And sorting is useful for the result to be deterministic, ideally it should be based on a set of fields with unique values. - D-side
  • I suppose this information is related to rollback logging and logging, and is not related to the ideas of relational databases. I agree that sorting coupled with limit apparently will have the expected effect. - mikivan
  • In relational databases, if there is sorting by a unique key, the notion of the order of records is. With no sorting, no. If the sorting key is non-unique, it is up to the group level and not inside the group. - Akina