How to select bottom most rows?

Question

I can do SELECT TOP (200) ... but why not BOTTOM (200)?

Well not to get into philosophy what I mean is, how can I do the equivalent of TOP (200) but in reverse (from the bottom, like you'd expect BOTTOM to do...)?

Answer 1

Sorry, but I don't think I see any correct answers in my opinion.

The TOP x function shows the records in undefined order. From that definition follows that a BOTTOM function can not be defined.

Independent of any index or sort order. When you do an ORDER BY y DESC you get the rows with the highest y value first. If this is an autogenerated ID, it should show the records last added to the table, as suggested in the other answers. However:

• This only works if there is an autogenerated id column
• It has a significant performance impact if you compare that with the TOP function

The correct answer should be that there is not, and cannot be, an equivalent to TOP for getting the bottom rows.

Answer 2

"Tom H" answer above is correct and it works for me in getting Bottom 5 rows.

SELECT [KeyCol1], [KeyCol2], [Col3]
FROM
(SELECT TOP 5 [KeyCol1],
       [KeyCol2],
       [Col3]
  FROM [dbo].[table_name]
  ORDER BY [KeyCol1],[KeyCol2] DESC) SOME_ALAIS
  ORDER BY [KeyCol1],[KeyCol2] ASC

Thanks.

Answer 3

It is unnecessary. You can use an ORDER BY and just change the sort to DESC to get the same effect.

Answer 4

I've come up with a solution to this that doesn't require you to know the number of row returned.

For example, if you want to get all the locations logged in a table, except the latest 1 (or 2, or 5, or 34)

SELECT * 
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Row, * 
    FROM Locations
    WHERE UserId = 12345) AS SubQuery
WHERE Row > 1 -- or 2, or 5, or 34

Answer 5

The currently accepted answer by "Justin Ethier" is not a correct answer as pointed out by "Protector one".

As far as I can see, as of now, no other answer or comment provides the equivalent of BOTTOM(x) the question author asked for.

First, let's consider a scenario where this functionality would be needed:

SELECT * FROM Split('apple,orange,banana,apple,lime',',')

This returns a table of one column and five records:

• apple
• orange
• banana
• apple
• lime

As you can see: we don't have an ID column; we can't order by the returned column; and we can't select the bottom two records using standard SQL like we can do for the top two records.

Here is my attempt to provide a solution:

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
SELECT TOP 2 * FROM #mytemptable ORDER BY tempID DESC
DROP TABLE #mytemptable

And here is a more complete solution:

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
DELETE FROM #mytemptable WHERE tempID <= ((SELECT COUNT(*) FROM #mytemptable) - 2)
ALTER TABLE #mytemptable DROP COLUMN tempID
SELECT * FROM #mytemptable
DROP TABLE #mytemptable

I am by no means claiming that this is a good idea to use in all circumstances, but it provides the desired results.

Answer 6

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