select top2 *
from mytable
order by id desc

select top1 * from mytable
where
id > (select min(id) from mytable)
order by id desc

CREATE PROCEDURE GetSortedPage(
  @TableName VARCHAR(50),
  @PrimaryKey VARCHAR(25),
  @SortField VARCHAR(100),
  @PageSize INT,
  @PageIndex INT = 1,
  @QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0
GO

select top 1 * from
(
 select top 2 *
 from mytable
 order by id desc
) t
order by id;

with
  t (num, name) as
    (
      select 1, 'Анна' union all
      select 2, 'Василий' union all
      select 3, 'Дима'
    )
select top 1 * 
  from (
         select top 2 t.*
           from t
          order by name desc
       ) v
 order by name;


num    name
---   -------
  2   Василий

(1 row(s) affected)

declare @t table (idx int, [name] varchar(max))

insert @t
      select 22, 'Анна' union all
      select 45, 'Василий' union all
      select 14, 'Дима' union all
      select 50, 'Александр' union all
      select 31, 'Борис'

select * from @t

-- Найти просто с предпоследним idx. (Estimated Subtree Cost = 0,0098605)
select * from @t where idx = (select max(idx) from @t where idx < (select max(idx) from @t))

-- Вывести предпоследнюю и последнюю записи (Estimated Subtree Cost = 0,006572)
select * from @t where idx in ( (select max(idx) from @t where idx < (select max(idx) from @t)), (select max(idx) from @t) )


/* Тоже самое, но через сортировку */
-- (Estimated Subtree Cost = 0,0179371)
select top 1 * from @t where idx < (select max(idx) from @t) order by idx desc

-- (Estimated Subtree Cost = 0,0260138)
select top 2 * 
  from (
         select top 2 t.*
           from @t t
          order by idx desc
       ) v
 order by idx

select * from Table1 where id in (select top (select (100-count(*)) from Table1  where state=1)  id 
from Table1   where state=0 order by id desc) or state=1  order by id

select * from Table1 
  where id in (select top (100)  id from Table1  where state=0 order by id desc)
    or state=1  order by id

in (...)

select *
from (
select *, ROW_NUMBER() over (order by state desc, id desc) as num
	from (
		select * 
		from Table1
		where state = 1
		union all
		select *
		from (
			select top 100 *
			from Table1
			where state = 0
			order by id desc
		) as t
	) as t
) as t
where state = 1 or num <= 100

declare @cnt int

select @cnt=CASE WHEN count(*)<100 then 100 ELSE count(*) END FROM aTable WHERE state=cast( 1 as bit)

set ROWCOUNT @cnt

select * FROM aTable WHERE state=cast( 1 as bit)
union all
select * 
FROM
(select TOP 100 * FROM aTable WHERE state=cast( 0 as bit) order by id) X
order by state desc, id

with
  t (id, global_id, summa, date_ins) as
    (
		select 1, 1000, 100, '16.09.2017' union all
		select 2, 2000, 200, '16.09.2017' union all
		select 3, 3000, 300, '16.09.2017' union all
		select 4, 1000, 150, '16.10.2017' union all
		select 5, 2000, 250, '16.10.2017' union all
		select 6, 3000, 350, '16.10.2017' union all
		select 7, 1000, 100, '16.11.2017' union all
		select 8, 2000, 200, '16.11.2017' union all
		select 9, 3000, 300, '16.11.2017' 
    )

select top(2) t0.*
  from t t0
  LEFT join T t1
    on t0.id < t1.id
   and t0.summa = t1.summa
 where t1.id is null
 order by t0.id desC

id	global_id	summa	date_ins
9	3000	300	2017-11-16
8	2000	200	2017-11-16