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

User · Answer

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  lt     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

User · Answer

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

User · Answer

First  create an index in a subquery according to the table s original order using    ROW NUMBER    OVER  ORDER BY  SELECT NULL    AS RowIndex   Then order the table descending by the RowIndex column you ve created in the main query    ORDER BY RowIndex DESC   And finally use TOP with your wanted quantity of rows       SELECT TOP 1   -- or 2  or 5  or 34      FROM    SELECT ROW NUMBER   OVER  ORDER BY   SELECT NULL    AS RowIndex                 FROM MyTable  AS SubQuery     ORDER BY RowIndex DESC

User · Answer

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  lt   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

User · Answer

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

User · Answer

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  gt  1 -- or 2  or 5  or 34

User · Answer

Logically  BOTTOM  x  is all the records except TOP  n - x   where n is the count  x  lt   n  E g  Select Bottom 1000 from Employee  In T-SQL  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

User · Answer

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  gt   NumberOfRows - 20

User · Answer

SELECT TOP 10 from TABLE1 ORDER BY ID DESC   Where ID is the primary key of the TABLE1

User · Answer

SELECT     columns FROM        SELECT TOP 200           columns      FROM           My Table      ORDER BY           a column DESC   SQ ORDER BY      a column ASC

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the OFFSET FETCH clause  SELECT COUNT 1  FROM COHORT  --Number of results to expect  SELECT   FROM COHORT  ORDER BY ID OFFSET 900 ROWS --Assuming you expect 1000 rows FETCH NEXT 100 ROWS ONLY    This is for Microsoft SQL Server  Official documentation  https   www sqlservertutorial net sql-server-basics sql-server-offset-fetch

User · Answer

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

[sql] How to select bottom most rows?

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to select

Examples related to keyword