Как отфильтровать первую и последнюю запись из таблицы с помощью RANK?

Question

У меня есть эти данные:

Id   Date  Value
'a'  2000  55
'a'  2001  3
'a'  2012  2
'a'  2014  5
'b'  1999  10
'b'  2014  110
'b'  2015  8
'c'  2011  4
'c'  2012  33

Я хочу отфильтровать первое и последнее значение (когда таблица отсортирована по столбцу "Дата") и сохранить только другие значения. Если есть только две записи, ничего не возвращается. (Пример для Id = 'c')

ID   Date  Value
'a'  2001  3
'a'  2012  2
'b'  2014  110 

Я пытался использовать порядок по (RANK() OVER (PARTITION BY [Id] ORDER BY Date ...)) в сочетании с этой статьей ( http://blog.sqlauthority.com/2008/03/02/sql-server-how-to-retrieve-top-and-bottom-rows-together-using-t-sql/), но я не могу заставить его работать.

[ОБНОВЛЕНИЕ]
Все 3 ответа кажутся нормальными. Но я не эксперт по SQL, поэтому мой вопрос в том, какая из них имеет наибольшую производительность, если в таблице около 800000 строк и нет индексов ни по одному столбцу.

Answer 1

Вы можете сделать это с EXISTS:

SELECT *
FROM Table1 a
WHERE EXISTS (SELECT 1
                  FROM Table1 b
                  WHERE a.ID = b.ID
                   AND b.Date < a.Date
                  )
  AND EXISTS (SELECT 1
                  FROM Table1 b
                  WHERE a.ID = b.ID
                   AND b.Date > a.Date
                  )

Демо: SQL Fiddle

Answer 2

Ты можешь использовать row_number дважды, чтобы определить min а также max даты, а затем фильтровать соответственно:

with cte as (
  select id, [date], value, 
    row_number() over (partition by id order by [date]) minrn,
    row_number() over (partition by id order by [date] desc) maxrn
  from data
  )
select id, [date], value
from cte
where minrn != 1 and maxrn != 1

• SQL Fiddle Demo

---

Вот еще один подход с использованием min а также max для этого не нужно использовать функцию ранжирования:

with cte as (
  select id, min([date]) mindate, max([date]) maxdate
  from data
  group by id 
  )
select * 
from data d
where not exists (
    select 1
    from cte c
    where d.id = c.id and d.[date] in (c.mindate, c.maxdate))

• Больше скрипки

Answer 3

Вот аналогичное решение с row_number а также count:

SELECT id,
       dat,
       value
FROM   (SELECT *,
               ROW_NUMBER()
                 OVER(
                   partition BY id
                   ORDER BY dat)    rnk,
               COUNT(*)
                 OVER (
                   partition BY id) cnt
        FROM   @table) t
WHERE  rnk NOT IN( 1, cnt )