need a query

• nithiraja.r

  Newbie

  Points: 9

  More actions

  June 21, 2012 at 12:44 pm

  #276826

  NOMark1Mark2descriplatest

  11112322aaa1

  11112422aaa1

  22222522bbb0

  22222521bbb0

  33332520ccc1

  Here the NO column is not a identity column. I need to write a query to populate unique NO from the table which has highest mark1.if the mark1 value is same to all of that particular NO then I need to take highest mark2.

  My expected result is below

  NOMark1Mark2descriplatest

  11112422aaa1

  22222522bbb0

  33332520ccc1

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 1:23 pm

  #1504192

  nithiraja.r (6/21/2012)

  ---

  NOMark1Mark2descriplatest

  11112322aaa1

  11112422aaa1

  22222522bbb0

  22222521bbb0

  33332520ccc1

  Here the NO column is not a identity column. I need to write a query to populate unique NO from the table which has highest mark1.if the mark1 value is same to all of that particular NO then I need to take highest mark2.

  My expected result is below

  NOMark1Mark2descriplatest

  11112422aaa1

  22222522bbb0

  33332520ccc1

  Give this a try. You will need change it to use your table.

  with basedata as (

  select

  NO,

  Mark1,

  Mark2,

  descrip,

  latest,

  row_number() over (partition by NO order by Mark1 desc, Mark2 desc) rn

  from

  dbo.tablename

  )

  select

  NO,

  Mark1,

  Mark2,

  descrip,

  latest

  from

  basedata

  where

  rn = 1;