In Teradata I need to select the first record for a group as well as the second to last record for the same group for multiple groups with other set conditions. How can I acheive this?

ex table:

Desired Output:

CodePudding user response：

Not tested, just an idea.

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

CodePudding user response：

This works if you're ok with specifying each group manually:

(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);

(looking into a more generic solution atm)

CodePudding user response：

I would do something like this:

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"

CodePudding user response：

Should work , not tested

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