select to 2 resords for each record

• polo.csit

  SSCrazy

  Points: 2574

  More actions

  April 18, 2009 at 1:13 am

  #136793

  Hi all,

  these is my result bu i want result

  ID count

  1 10

  1 20

  1 30

  1 40

  1 5

  1 8

  2 8

  2 77

  2 66

  2 33

  2 99

  2 22

  3 44

  3 33

  3 22

  3 44

  3 99

  3 366

  but i want result...

  select top 2records for each unique id

  ID count

  1 10

  1 20

  2 8

  2 77

  3 44

  3 33

  regards pols

• arun.sas

  SSChampion

  Points: 11831

  More actions

  April 18, 2009 at 1:54 am

  #980128

  Hi Pols,

  Try this code

  create table #ABC

  (

  id1 int,

  count1 int

  )

  insert into #ABC values (1,10)

  insert into #ABC values (1,20)

  insert into #ABC values (1,30)

  insert into #ABC values (1,40)

  insert into #ABC values (2,10)

  insert into #ABC values (2,20)

  insert into #ABC values (2,30)

  insert into #ABC values (3,40)

  insert into #ABC values (3,5)

  select a.* from #ABC a

  where a.count1 in (select top 2/*plot your count top 1,2,3..*/ b.count1 from #ABC b where b.id1 = a.id1)

  order by a.id1,a.count1 /*if needed use the order*/

  ARUN SAS