如何选择SQL中给定组的第一个和第二个记录？

Question

在Teradata中，我需要选择一个组的第一个记录，以及与其他设置条件的多个组的同一组的第二个记录。我该如何获得这个？

例如

ID	记录	日期	1
1	​	2022-01-12	1
ONE	2	2022-01-12	1
ONE	3	2022-01-12	1
ONE	4	2022-01-12	1
ONE	1	2022-01-12	2
组	：	2022-01-12	1
两	2	2022-01-12	1
两个	3	2022-01-12	1
两	TWE	2022-01-12	1
4	TAT	​	​
两	5	2022-01-12	1
TWE	​	​05-12	1
两个	6	2022-05-12	1

的输出

ID	记录	日期	位置
1	1	2022-01-12	1
ONE	3	2022-01-12	1
TWET	1	TWE222-01-12	TWE
：	所需	组 12	1

Answer 1

我会做这样的事情：

select
*
from
table
qualify row_number() over (partition by groupid order by date ASC) = 1 --"first"
or  row_number() over (partition by groupid order by date DESC) = 2 -- "second to last"

Answer 2

没有测试，只是一个主意。

select q.*
from
(
select t.*,
max(t.records)-1 over (partition by t.group_id) as mxprev
from yourtable as t
) as q
where q.records=1 or q.records=q.mxprev

Answer 3

如果您可以手动指定每个组，则可以使用：（

(SELECT * FROM extable 
WHERE groupid = 'One'
ORDER BY date ASC -- or whatever you want to order by to get "first" and "second to last" 
LIMIT 1) 

UNION 

(SELECT * FROM extable 
WHERE groupid = 'One'
ORDER BY date DESC -- or whatever you want to order by to get "first" and "second to last"
LIMIT 1 
OFFSET 1)

UNION 

(SELECT * FROM extable 
WHERE groupid = 'Two'
ORDER BY date ASC -- or whatever you want to order by to get "first" and "second to last" 
LIMIT 1) 

UNION 

(SELECT * FROM extable 
WHERE groupid = 'Two'
ORDER BY date DESC -- or whatever you want to order by to get "first" and "second to last"
LIMIT 1 
OFFSET 1);

查看一个更通用的解决方案ATM）

Answer 4

应该工作，不测试

select * from 
  (select *
   ,row_number() over(partition by group id order by records) rn1
   from table1
  ) t1 where t1 = 1
union all
select * from 
(
   select *
    ,row_number() over(partition by group id order by records desc) rn2
   from table1
) t2 where  rn2 = 2