All Forums General
ksaikrishna 26 posts Joined 08/11
09 Jul 2012
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

 

 

dnoeth 4628 posts Joined 11/04
09 Jul 2012

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

Dieter

ksaikrishna 26 posts Joined 08/11
09 Jul 2012

Thanks Dnoeth.

You must sign in to leave a comment.