0

Data:

Name  Desc
Daman XXXX
Sam   XXXX
Ram   XXXX
Sun   XXXX

Output:

Name Desc
Sun  XXXX
Ram  XXXX

There is no identity column. So far I have tried the below but its not helpful

select *, Rank() over(order by Name ) as [Rank] from testdaman
James Z
  • 12,209
  • 10
  • 24
  • 44
nik_boyz
  • 165
  • 3
  • 15
  • 4
    To logically pick the last 2 rows, you need to specify an order. In this case, there is no guaranteed way of extracting `Ram` and `Sun`, unless you have a way of expressing an order that makes it so those 2 rows are the first (or last) 2 rows of a query. – sstan Aug 17 '15 at 18:08
  • 2
    Sorry, but you can't. You need to use an auto-increment or timestamp to know the inserted order. – Andrew Paes Aug 17 '15 at 18:10
  • 4
    Impossible unless you add a column (like an IDENTITY column, or a datetime column with default current_timestamp). SQL Server has no concept of "last row"... a table is an unordered set of rows. So, if you want to reliably pull the last two rows, you need to change the table (or add a trigger that logs inserts somewhere). – Aaron Bertrand Aug 17 '15 at 18:11
  • @AaronBertrand datetime could give him a colision of results, due transaction inserting way, timestamp would be a better choice. – Andrew Paes Aug 17 '15 at 18:12
  • if I specify an order so the result will get change if a new row is added with a different alphabetical order. – nik_boyz Aug 17 '15 at 18:13
  • @AndrewPaes Ties can be dealt with easily - this is why we have `WITH TIES` in the syntax, for example. You can also use `DATETIME2` for more granularity and much less chance for collision. `TIMESTAMP` isn't the answer - you do know that `TIMESTAMP` in SQL Server actually contains *zero* information about date or time, right? – Aaron Bertrand Aug 17 '15 at 18:15
  • 1
    @nik_boyz: If you don't have a reliable column value that provides you an order, then your rows are ***not*** ordered, as much as it may seem so. Unless you specify an `ORDER BY` clause, you have no guarantees that your rows will always be returned in the same order. You're asking for trouble if you make that assumption. – sstan Aug 17 '15 at 18:15
  • http://stackoverflow.com/questions/20050341/when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record – sstan Aug 17 '15 at 18:17
  • In sql server 2008 is an undocumented and unsupported %%physloc%% column. Maybe this could help you. – Michał Szkudlarek Aug 17 '15 at 18:52
  • 1
    Also `TIMESTAMP` (better called `ROWVERSION`) columns change on update. Thus they would be unreliable for determining anything about initial insert of a row. – Shannon Severance Aug 17 '15 at 18:54
  • @MichałSzkudlarek How does that help you? Physical ordering != logical ordering, nor is it reliable to indicate time of insert. – Aaron Bertrand Aug 17 '15 at 19:28
  • possible duplicate of [SQL get last rows in table WITHOUT primary ID](http://stackoverflow.com/questions/5870853/sql-get-last-rows-in-table-without-primary-id) – Tab Alleman Aug 17 '15 at 19:35

1 Answers1

2

order by (select null) is not actually ordering your records, it is used because row_number() requires an over and order by clause. No order is guaranteed, but you may find that the order is the same as the order the records were inserted in.

select top 2 *
from
(
  select *, row_number() over (order by (select null)) [row]
  from testdaman
) T
order by [row] desc

Original Answer - requires SQL Server 2012 and above

select *
from testdaman
order by (select null) -- No order applied, but offset requires the order by line
offset (select count(*)-2 from testdaman) rows fetch next (2) rows only
Aducci
  • 26,101
  • 8
  • 63
  • 67