Logic for resolving issue

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  November 7, 2013 at 7:15 am

  #282223

  Hi Expert,

  I need your help for resolving my one issue

  Table script,

  create table tmp_medal

  (medal_name nvarchar(50) null,

  winner_id int null)

  insert statements,

  insert into tmp_medal values ('Blue',123)

  insert into tmp_medal values ('Bronze',123)

  insert into tmp_medal values ('Silver',123)

  insert into tmp_medal values ('Gold',123)

  The output I would like to see for 123

  winner_id medal_name OutPut

  123 'Blue' 'Gold'

  123 'Bronze' 'Gold'

  123'Silver' 'Gold'

  123'Gold' 'Gold'

  insert into tmp_medal values ('Blue',456)

  insert into tmp_medal values ('Bronze',456)

  insert into tmp_medal values ('Silver',456)

  The output I would like to see for 456

  winner_id medal_name OutPut

  456 'Blue' 'Silver'

  456 'Bronze' 'Silver'

  456'Silver' 'Silver'

  insert into tmp_medal values ('Blue',789)

  insert into tmp_medal values ('Bronze',789)

  The output I would like to see for 789

  winner_id medal_name OutPut

  789 'Blue' 'Bronze'

  789 'Bronze' 'Bronze'

  insert into tmp_medal values ('Blue',100)

  The output I would like to see for 100

  winner_id medal_name OutPut

  100 'Blue' 'Blue'

• Keith Tate

  SSCrazy Eights

  Points: 9888

  More actions

  November 7, 2013 at 7:27 am

  #1664757

  I would create a Medal table that had each medal and their rank (gold = 1, silver = 2, etc.) then you can use something like this:

  create table Medal (tmp_medal_id int, tmp_medal nvarchar(50))

  insert into Medal

  values (1,'Gold')

  ,(2,'Silver')

  ,(3,'Bronze')

  ,(4,'Blue')

  with topMedal as

  (

  select a1.winner_id, min(a2.tmp_medal_id) topMedalID

  from tmp_medal a1

  join medal a2 on a1.medal_name = a2.tmp_medal

  group by a1.winner_id)

  select

  a.winner_id

  ,medal_name

  ,m.tmp_medal

  from tmp_medal a

  join topMedal tm on a.winner_id = tm.winner_id

  join medal m on tm.topMedalID = m.tmp_medal_id

  Shame on me for not using 2 part naming, but I was in a hurry. 😛

  ---

  
  Microsoft Certified Master - SQL Server 2008
  Follow me on twitter: @keith_tate

  Forum Etiquette: How to post data/code on a forum to get the best help[/url]