select 'first' five non null values based on another non-sequential field

• Ulysses_Infinity

  SSCrazy

  Points: 2588

  More actions

  July 16, 2009 at 4:07 pm

  #134270

  Hi,

  drop table #temp1

  create table #temp1

  (

  IDint,

  Codevarchar(15)

  )

  insert into #temp1 (ID,Code)

  select 1,'France' union all

  select 2,'Spain' union all

  select 3,null union all

  select 4,'England' union all

  select 5,'Germany' union all

  select 6,null union all

  select 7,'Scotland' union all

  select 8,'Ireland' union all

  select 9,'Wales' union all

  select 10,null union all

  select 11,'Denmark' union all

  select 12,'Holland' union all

  select 13,null union all

  select 14,null union all

  select 15,'USA' union all

  select 16,'Mexico' union all

  select 17,null union all

  select 18,'Australia' union all

  select 19,'Japan' union all

  select 20,null union all

  select 21,'China' union all

  select 22,'Korea' union all

  select 23,'Iran' union all

  select 24,null union all

  select 25,'Iraq'

  A table has two fields: ID (int), Country (varchar)

  ID is sequential and cannot be null, Country can be null.

  I want to select the 'first' five non-null Countrys, but following a specific ID sequence.

  I want to select the 'first' five non-null Countrys from this sequence in ID values:

  14, 8, 3, 10, 12, 17, 13, 16, 25, 23, 24 But based on that specific order.

  So based on the above ID sequence the 'first' five non-null Countrys would be:

  Ireland,

  Holland,

  Mexico,

  Iraq,

  Iran <-- in that order I'm using SS2K . Any help would be greatly appreciated.
  Many thanks,

  Jason
  ---

• Ulysses_Infinity

  SSCrazy

  Points: 2588

  More actions

  July 16, 2009 at 4:36 pm

  #1025948

  I've figured out one way:

  select top 5 ID,

  Country

  from #temp1

  where ID in (14, 8, 23, 3, 10, 12, 17, 13, 16, 25, 23, 24) and Country is not null

  order by case ID

  when 14 then 1

  when 8 then 2

  when 23 then 3

  when 3 then 4

  when 10 then 5

  when 12 then 6

  when 17 then 7

  when 13 then 8

  when 16 then 9

  when 25 then 10

  when 23 then 11

  when 24 then 12

  end

  But would be interested in other solutions.

  Cheers...Jason

• tombra

  SSC Enthusiast

  Points: 153

  More actions

  July 16, 2009 at 4:58 pm

  #1025954

  What about:

  create table #seq

  (

  ID int IDENTITY(1,1),

  Country_id int

  )

  insert into #seq

  select 14 union all

  select 8 union all

  select 3 union all

  select ....

  select top 5

  T.Country

  from #temp1 T

  inner join #seq S on T.ID = S.Country_id

  where T.Country is not null

  order by S.ID

  Kind Regards

  Tomek