Analog LIMIT in MSSQL
It just so happened that MSSQL does not have its analogue of the LIMIT function in MySQL (with the exception of TOP). But quite often there is a need to choose a certain range of values not from the first but, for example, from the 1000th.
There are several possible options:
1. We select the values from the first to the last needed, transfer them to the script and there we cut off those that we do not need when outputting .
n - number of the last element
id - unique identifier
When working with large tables it takes a very long time to execute, it is suitable only for small tables
2. We make a selection with the condition of not entering on the basis of n - number of the last element m - required number of elements id - unique identifier
It works in some cases a little faster than the first method and is suitable for processing large enough tables under the condition of a non-complex query
3. Finally, the last method using row_number (), which works most quickly with almost any table n - the number of the last element m - the required number of id elements - unique identifier A small paradox was noticed yesterday; sampling in method 3 worked much faster than just SELECT TOP n from the same table (in which there are more than 60 million rows). PS This is my first topic on the hub, please do not judge strictly.
There are several possible options:
1. We select the values from the first to the last needed, transfer them to the script and there we cut off those that we do not need when outputting .
SELECT TOP n * FROM TABLE ORDER BY id
n - number of the last element
id - unique identifier
When working with large tables it takes a very long time to execute, it is suitable only for small tables
2. We make a selection with the condition of not entering on the basis of n - number of the last element m - required number of elements id - unique identifier
SELECT TOP n id,* FROM TABLE
WHERE id not in (SELECT TOP (n - m) id FROM TABLE)
ORDER BY id
It works in some cases a little faster than the first method and is suitable for processing large enough tables under the condition of a non-complex query
3. Finally, the last method using row_number (), which works most quickly with almost any table n - the number of the last element m - the required number of id elements - unique identifier A small paradox was noticed yesterday; sampling in method 3 worked much faster than just SELECT TOP n from the same table (in which there are more than 60 million rows). PS This is my first topic on the hub, please do not judge strictly.
WITH num_row
AS
(
SELECT row_number() OVER (ORDER BY id) as nom , *
FROM TABLE
)
SELECT * FROM num_row
WHERE nom BETWEEN (n - m) AND n