how to get the desired output correctly

• iiit.raju

  Old Hand

  Points: 348

  More actions

  September 19, 2013 at 6:29 am

  #298935

  i have a table emp with records as follows:

  Create table testemp

  (

  empname varchar(50),

  highprtjobs varchar(50)

  )

  insert into testemp values ('tsaliki','h1')

  insert into testemp values ('tsaliki','h4')

  insert into testemp values ('sasi','h2')

  insert into testemp values ('sasi','h5')

  insert into testemp values ('srinivas','h3')

  select * from testemp

  in this i wrote a select statement as follows:

  select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empname. i got the result as

  empname noofhighprtjobs

  sasi 2

  srinivas 1

  tsaliki 2

  In this now i am trying to get the one which is having min noofhighprtjobs( here srinivas result i should get it as output) So i wrote the above statement but it is throwing some error.So how do i get the empname whose noofhighprtjobs is minimum.

  select empname,count(highprtjobs)as noofhighprtjobs from testemp group by empname

  having min(count(highprtjobs))

• Koen Verbeeck

  SSC Guru

  Points: 259125

  More actions

  September 19, 2013 at 6:37 am

  #1651480

  SELECT TOP 1

  empname

  ,noofhighprtjobs = COUNT(highprtjobs)

  FROM testemp

  GROUP BY empname

  ORDER BY noofhighprtjobs

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP