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

Asked

Viewed 472 times

0

During my studies, I tried to sort the records and put those that are NULL in the end, I did it this way:

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 had no interest in putting the TOP, but found that when using a query inside the FROM, it is mandatory to put the TOP (at least in the version I use), it happens that the TOP expects 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!

sql sql-server
  • 1

    I’ve never seen TOP mandatory in a sub-select, explain it better there

  • i also do not believe it is necessary. But you can use the modifier PERCENT to specify that you want a percentage, not a fixed number. Then just do SELECT TOP (100) PERCENT.

  • Gabriel, in the sub-sale TOP was requested because you put ORDER BY in it.

1 answer

1


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

TOP (Transact-SQL):

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

Another issue is that there is no need to do two select to separate the records and then re-merge them just in order to sort them. You can solve your problem by just putting a CASE WHEN in his order by, See the example below:

Sqlfiddle - Online example:

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

inserir a descrição da imagem aqui

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.