How to display first 3 and last 3 records from a table

General
Enthusiast

How to display first 3 and last 3 records from a table

Hi All,

Can someone help me how to display the first & last 3 records from atable?

Ex:

id     name

101   sai

102    kris

103    pav

104    ram

105    kab

106     xyz

107     abc

108   jqk

109    tan

110  DAn

Required output:

id     name  sno

101   sai      1

102    kris    1

103    pav    1

108   jqk      2

109    tan     2

110  DAn      2

Thanks,

Sai

2 REPLIES
Senior Apprentice

Re: How to display first 3 and last 3 records from a table

Avoiding two ROW_NUMBERs (asc/desc) which would result in two Explain steps:

select t.*,  
row_number() over (order by id) as x
from tab as t
qualify x <= 3
or x > count(*) over () -3

Dieter

Enthusiast

Re: How to display first 3 and last 3 records from a table

Thanks Dnoeth.