SELECT TOP that brings all the results, something like TOP all, exists in SQL Server?

Navigation
0

During my studies, I tried to sort the records and put the ones that are NULL at the end, I did the following: 

SELECT 
    tbl1.firstname,
    tbl1.mgrid
FROM 
    (
        SELECT TOP 100 firstname, mgrid
        FROM HR.Employees
        WHERE mgrid IS NOT NULL
        ORDER BY mgrid  
    ) tbl1
UNION ALL
SELECT 
    tbl2.firstname,
    tbl2.mgrid
FROM 
    (
        SELECT TOP 100 firstname, mgrid
        FROM HR.Employees
        WHERE mgrid IS NULL
        ORDER BY mgrid  
    ) tbl2

I was not interested in putting TOP , but I discovered that when using a query within FROM , it is mandatory to set TOP (at least in the version I use), TOP a quantity parameter, but I do not have a fixed quantity, so I would like to know if there is something like SELECT TOP all .

Thank you!

    
asked by anonymous 29.03.2018 / 16:06

1 answer

1

The TOP () command is intended to limit the rows returned in a set, so it makes no sense to use it to return all rows.

TOP (Transact-SQL) :

  

Limits the rows returned in a query result set to a number or percentage of rows

Another issue is that there is no need to make two select to separate the records and then join them again just in order to sort them. You can solve your problem by just putting a CASE WHEN on your order by , see the example below:

SQLFiddle - Online sample : 

SELECT firstname, mgrid
FROM Employees
ORDER BY
   CASE WHEN mgrid IS NULL 
     THEN 1 
     ELSE 0 
   END

    
29.03.2018 / 16:33
How to close a modal by pressing the "ENTER" Script Generate Excel SQL SERVER