How to Select Bottom Most Rows

How to select bottom most rows?

SELECT
    columns
FROM
(
     SELECT TOP 200
          columns
     FROM
          My_Table
     ORDER BY
          a_column DESC
) SQ
ORDER BY
     a_column ASC

Select top and bottom rows

Using a union is the only thing I can think of to accomplish this

select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b

Why is there no `select last` or `select bottom` in SQL Server like there is `select top`?

You can think of it like this.

SELECT TOP N without ORDER BY returns some N rows, neither first, nor last, just some. Which rows it returns is not defined. You can run the same statement 10 times and get 10 different sets of rows each time.

So, if the server had a syntax SELECT LAST N, then result of this statement without ORDER BY would again be undefined, which is exactly what you get with existing SELECT TOP N without ORDER BY.

---

You have stressed in your question that you know and understand what I've written below, but I'll still keep it to make it clear for everyone reading this later.

Your first phrase in the question

In SQL-server we have SELECT TOP N ... now in that we can get the
first n rows in ascending order (by default), cool.

is not correct. With SELECT TOP N without ORDER BY you get N "random" rows. Well, not really random, the server doesn't jump randomly from row to row on purpose. It chooses some deterministic way to scan through the table, but there could be many different ways to scan the table and server is free to change the chosen path when it wants. This is what is meant by "undefined".

The server doesn't track the order in which rows were inserted into the table, so again your assumption that results of SELECT TOP N without ORDER BY are determined by the order in which rows were inserted in the table is not correct.

---

So, the answer to your final question

why no select last/bottom like it's counterpart.

is:

• without ORDER BY results of SELECT LAST N would be exactly the same as results of SELECT TOP N - undefined.
• with ORDER BY result of SELECT LAST N ... ORDER BY X ASC is exactly the same as result of SELECT TOP N ... ORDER BY X DESC.

So, there is no point to have two key words that do the same thing.

---

There is a good point in the Pieter's answer: the word TOP is somewhat misleading. It really means LIMIT result set to some number of rows.

By the way, since SQL Server 2012 they added support for ANSI standard OFFSET:

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
  FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]

Here adding another key word was justified that it is ANSI standard AND it adds important functionality - pagination, which didn't exist before.

---

I would like to thank @Razort4x here for providing a very good link to MSDN in his question. The "Advanced Scanning" section there has an excellent example of mechanism called "merry-go-round scanning", which demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

This concept is often misunderstood and I've seen many question here on SO that would greatly benefit if they had a quote from that link.

---

The answer to your question

Why doesn't SQL Server have a SELECT LAST or say SELECT BOTTOM or
something like that, where we don't have to specify the ORDER BY and
then it would give the last record inserted in the table at the time
of executing the query (again I am not going into details about how
would this result in case of uncommitted reads or phantom reads).

is:

The devil is in the details that you want to omit. To know which record was the "last inserted in the table at the time of executing the query" (and to know this in a somewhat consistent/non-random manner) the server would need to keep track of this information somehow. Even if it is possible in all scenarios of multiple simultaneously running transactions, it is most likely costly from the performance point of view. Not every SELECT would request this information (in fact very few or none at all), but the overhead of tracking this information would always be there.

So, you can think of it like this: by default the server doesn't do anything specific to know/keep track of the order in which the rows were inserted, because it affects performance, but if you need to know that you can use, for example, IDENTITY column. Microsoft could have designed the server engine in such a way that it required an IDENTITY column in every table, but they made it optional, which is good in my opinion. I know better than the server which of my tables need IDENTITY column and which do not.

Summary

I'd like to summarise that you can look at SELECT LAST without ORDER BY in two different ways.

1) When you expect SELECT LAST to behave in line with existing SELECT TOP. In this case result is undefined for both LAST and TOP, i.e. result is effectively the same. In this case it boils down to (not) having another keyword. Language developers (T-SQL language in this case) are always reluctant to add keywords, unless there are good reasons for it. In this case it is clearly avoidable.

2) When you expect SELECT LAST to behave as SELECT LAST INSERTED ROW. Which should, by the way, extend the same expectations to SELECT TOP to behave as SELECT FIRST INSERTED ROW or add new keywords LAST_INSERTED, FIRST_INSERTED to keep existing keyword TOP intact. In this case it boils down to the performance and added overhead of such behaviour. At the moment the server allows you to avoid this performance penalty if you don't need this information. If you do need it IDENTITY is a pretty good solution if you use it carefully.

SELECT BOTTOM without changing ORDER BY

You could just wrap it in another query and put in the order you require...

SELECT x.* 
FROM   (
       SELECT TOP 504 
              "date",
              price
       FROM   [dbo].[AssetRet]
       WHERE  asset = 'SP500'
       ORDER BY "date" DESC
       ) x 
ORDER BY x."date"

Does this alternative work?...you will need a later version of sql-server for the partition function RANK...

SELECT x."date",
       x.price
FROM   (
       SELECT "date",
              price,
              Rnk = RANK() OVER (ORDER BY "date" DESC)
       FROM  [dbo].[AssetRet]
       WHERE asset = 'SP500'
       ) x 
WHERE  x.Rnk <= 504
ORDER BY x."date" 

EDIT
Looks like this other answer pretty much covered your question

Select Bottom 30% of Rows by Group in SQL Server 2017

This would do I think:-

WITH RecordCount AS (
    SELECT ParentID, ROUND(COUNT(1)*.3, 0) RecordCount -- To be selected
    FROM MyData
    GROUP BY ParentID
), SortedRecords AS (
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY ID DESC) RowIndex
    FROM MyData
)
SELECT d.*
FROM MyData d
    JOIN SortedRecords sr ON d.ID = sr.ID
    JOIN RecordCount rc ON d.ParentID = rc.ParentID AND sr.RowIndex <= rc.RecordCount;

Select Top and Last rows in a table (SQL server)

To get the bottom 1000 you will want to order it by a column in descending order, and still take the top 1000.

SELECT TOP 1000 *
FROM [SomeTable]
ORDER BY MySortColumn DESC

If you care for it to be in the same order as before you can use a common table expression for that:

;WITH CTE AS (
    SELECT TOP 1000 *
    FROM [SomeTable]
    ORDER BY MySortColumn DESC
)

SELECT * 
FROM CTE
ORDER BY MySortColumn

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

Select bottom n records and concatenate in same row

Select last 20 rows when ordered by id. Return in ASC order, with a single query

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT top(20) *
            FROM table_x
            ORDER BY id DESC
        ) AS Latest_rec
        ORDER BY id ASC
        FOR XML PATH ('')), 1, 2, '') AS val;

How to select bottom N rows from each group - Oracle 11g

Try this:

WITH ordered 
     AS (SELECT qcd_outlet_code, 
                qcd_year, 
                qcd_quarter, 
                qcd_credit, 
                Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year DESC, qcd_quarter DESC) 
                AS rn 
         FROM   QTR_CREDIT_DATA) 
SELECT d.qcd_outlet_code    AS "Outlet_Code:string", 
       d.qcd_quarter 
       ||' ' 
       ||d.qcd_year         AS "MCT_quarter:string", 
       Nvl(d.qcd_credit, 0) AS "MCT_Total_Credits_Earned", 
       Row_number() 
                  over ( 
                    PARTITION BY qcd_outlet_code 
                    ORDER BY qcd_outlet_code, qcd_year, qcd_quarter)
                           AS "Display_Order:string" 
FROM   ordered d 
WHERE  rn <= 2; 

SQL Query select top and bottom rows from every batch in the table

Use row_number():

select *
from (
    select t.*, 
        row_number() over(partition by batchkey order by executiontime) rn_asc,
        row_number() over(partition by batchkey order by executiontime desc) rn_desc
    from mytable t
) t
where 1 in (rn_asc, rn_desc)

The logic is to rank records having the same batchkey by ascending and descending executiontime, then to filter on the the top and bottom row numbers. If there is just one record for a given batchkey, it gets ranked 1 in both directions.

---

Related Topics