How to select bottom most rows?

问题:

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...)?

回答1:

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

回答2:

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

回答3:

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.

回答4:

Select Bottom 1000 from Employee

DECLARE 
@bottom int,
@count int

SET @bottom = 1000 
SET @count = (select COUNT(*) from Employee)

select * from Employee emp where emp.EmployeeID not in 
(
SELECT TOP (@count-@bottom) Employee.EmployeeID FROM Employee
)

回答5:

All you need to do is reverse your ORDER BY. Add or remove DESC to it.

回答6:

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.

回答7:

The problem with ordering the other way is that it often does not make good use of indices. It is also not very extendable if you ever need to select a number of rows that are not at the start or the end. An alternative way is as follows.

DECLARE @NumberOfRows int;
SET @NumberOfRows = (SELECT COUNT(*) FROM TheTable);

SELECT col1, col2,...
FROM (
    SELECT col1, col2,..., ROW_NUMBER() OVER (ORDER BY col1) AS intRow
    FROM TheTable
) AS T
WHERE intRow > @NumberOfRows - 20;

回答8:

"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.

回答9:

It would seem that any of the answers which implement an ORDER BY clause in the solution is missing the point, or does not actually understand what TOP returns to you.

TOP returns an unordered query result set which limits the record set to the first N records returned. (From an Oracle perspective, it is akin to adding a where ROWNUM < (N+1).

Any solution which uses an order, may return rows which also are returned by the TOP clause (since that data set was unordered in the first place), depending on what criteria was used in the order by

The usefulness of TOP is that once the dataset reaches a certain size N, it stops fetching rows. You can get a feel for what the data looks like without having to fetch all of it.

To implement BOTTOM accurately, it would need to fetch the entire dataset unordered and then restrict the dataset to the final N records. That will not be particularly effective if you are dealing with huge tables. Nor will it necessarily give you what you think you are asking for. The end of the data set may not necessarily be "the last rows inserted" (and probably won't be for most DML intensive applications).

Similarly, the solutions which implement an ORDER BY are, unfortunately, potentially disastrous when dealing with large data sets. If I have, say, 10 Billion records and want the last 10, it is quite foolish to order 10 Billion records and select the last 10.

The problem here, is that BOTTOM does not have the meaning that we think of when comparing it to TOP.

When records are inserted, deleted, inserted, deleted over and over and over again, some gaps will appear in the storage and later, rows will be slotted in, if possible. But what we often see, when we select TOP, appears to be sorted data, because it may have been inserted early on in the table's existence. If the table does not experience many deletions, it may appear to be ordered. (e.g. creation dates may be as far back in time as the table creation itself). But the reality is, if this is a delete-heavy table, the TOP N rows may not look like that at all.

So -- the bottom line here(pun intended) is that someone who is asking for the BOTTOM N records doesn't actually know what they're asking for. Or, at least, what they're asking for and what BOTTOM actually means are not the same thing.

So -- the solution may meet the actual business need of the requestor...but does not meet the criteria for being the BOTTOM.

回答10:

SELECT TOP 10*from TABLE1 ORDER BY ID DESC

Where ID is the primary key of the TABLE1.

回答11:

try this.

declare @floor int --this is the offset from the bottom, the number of results to exclude
declare @resultLimit int --the number of results actually retrieved for use
declare @total int --just adds them up, the total number of results fetched initially

--following is for gathering top 60 results total, then getting rid of top 50. We only keep the last 10
set @floor = 50 
set @resultLimit = 10
set @total = @floor + @resultLimit

declare @tmp0 table(
    --table body
)

declare @tmp1 table(
    --table body
)

--this line will drop the wanted results from whatever table we're selecting from
insert into @tmp0
select Top @total --what to select (the where, from, etc)

--using floor, insert the part we don't want into the second tmp table
insert into @tmp1
select top @floor * from @tmp0

--using select except, exclude top x results from the query
select * from @tmp0
except 
select * from @tmp1

回答12:

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

回答13:

Querying a simple subquery sorted descending, followed by sorting on the same column ascending does the trick.

SELECT * FROM 
    (SELECT TOP 200 * FROM [table] t2 ORDER BY t2.[column] DESC) t1
    ORDER BY t1.[column]