how to get the rows with top count

• neliii

  Say Hey Kid

  Points: 701

  More actions

  May 21, 2010 at 11:51 am

  #224925

  i got a table with multiple rows

  Let us consider courses tabel

  courses Table:

  Course count specification author

  -----------------------------------------------

  digital 3 msse james

  AI 6 msse hacks

  digital 5 mdsa kali

  signal 8 mdfe jorge

  signal 7 mdfh kapil

  AI 2 msse hacks

  Music 10 misc tom

  Music 9 misc harry

  now i want to get the rows with top count based on course like

  course count specification author

  -----------------------------------------------

  digital 5 mdsa kali

  signal 8 mdfe jorge

  Music 10 misc tom

  AI 6 msse hacks

  create table courses

  (

  course varchar(100)

  count int()

  specification varchar(100)

  author varchar(100)

  )

  insert values into courses

  values('digital',3,'msse',james)

  insert values into courses

  values('AI',6,'msse','hacks')

  insert values into courses

  values('digital',5,'mdsa','kali')

  insert values into courses

  values('signal',8,'mdfe','jorge')

  insert values into courses

  values('signal',7,'mdfh','kapil')

  insert values into courses

  values('AI',2,'msse','hacks')

  insert values into courses

  values('Music',10,'misc','tom')

  insert values into courses

  values(Music 9 misc harry

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 21, 2010 at 12:30 pm

  #1171041

  1st of all I had to correct your table definition:

  create table #courses

  (course varchar(100),

  count int,

  specification varchar(100),

  author varchar(100))

  Then correct your input statements:

  insert into #courses

  values('digital',3,'msse','james')

  insert into #courses

  values('AI',6,'msse','hacks')

  insert into #courses

  values('digital',5,'mdsa','kali')

  insert into #courses

  values('signal',8,'mdfe','jorge')

  insert into #courses

  values('signal',7,'mdfh','kapil')

  insert into #courses

  values('AI',2,'msse','hacks')

  insert into #courses

  values('Music',10,'misc','tom')

  insert into #courses

  values('Music', 9, 'misc', 'harry')

  With the above completed here is the T-SQL you need

  ;with numbered as(SELECT rowno=row_number() over

  (Partition by course order by [count] DESC), Course,[count],specification,author

  FROM #courses)

  SELECT * FROM numbered WHERE rowno=1

  /*Results:

  rownoCoursecountspecificationauthor

  1AI6msse hacks

  1digital5mdsa kali

  1Music10misc tom

  1signal8mdfe jorge*/

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]