Need a little help on this query

• carlos.tapadinhas

  Ten Centuries

  Points: 1058

  More actions

  October 17, 2008 at 3:38 am

  #94147

  Hi to all

  CREATE TABLE SCHOOL (id INT , class VARCHAR(50), name VARCHAR(100), avaliation INT)

  INSERT INTO SCHOOL

  SELECT 1,1,'AAA',18

  INSERT INTO SCHOOL

  SELECT 2,1,'BBB',15

  INSERT INTO SCHOOL

  SELECT 3,1,'CCC',14

  INSERT INTO SCHOOL

  SELECT 4,1,'DDD',12

  INSERT INTO SCHOOL

  SELECT 5,1,'EEE',16

  INSERT INTO SCHOOL

  SELECT 6,1,'FFF',12

  INSERT INTO SCHOOL

  SELECT 7,1,'GGG',8

  INSERT INTO SCHOOL

  SELECT 21,2,'AAA',11

  INSERT INTO SCHOOL

  SELECT 22,2,'BBB',14

  INSERT INTO SCHOOL

  SELECT 23,2,'CCC',12

  INSERT INTO SCHOOL

  SELECT 24,2,'DDD',12

  INSERT INTO SCHOOL

  SELECT 25,2,'EEE',15

  INSERT INTO SCHOOL

  SELECT 26,2,'FFF',10

  INSERT INTO SCHOOL

  SELECT 27,2,'GGG',18

  INSERT INTO SCHOOL

  SELECT 31,3,'AAA',5

  INSERT INTO SCHOOL

  SELECT 32,3,'BBB',13

  INSERT INTO SCHOOL

  SELECT 33,3,'CCC',12

  INSERT INTO SCHOOL

  SELECT 34,3,'DDD',16

  INSERT INTO SCHOOL

  SELECT 35,3,'EEE',11

  INSERT INTO SCHOOL

  SELECT 36,3,'FFF',19

  INSERT INTO SCHOOL

  SELECT 37,3,'GGG',12

  I need to make a query, that gives me the best 3 students (avaliation) by class.. 3 for class 1, 3 for class 2 and more 3 for class 3...

  Can anybody help me?

  Thanks

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  October 17, 2008 at 4:13 am

  #886245

  try using row_number()

  ;WITH SchoolOrder AS

  (SELECT id,class,name,avaliation,

  ROW_NUMBER() OVER (PARTITION BY class ORDER BY avaliation DESC) AS TheOrder

  FROM school

  )

  SELECT * FROM schoolorder WHERE theorder <4

• carlos.tapadinhas

  Ten Centuries

  Points: 1058

  More actions

  October 17, 2008 at 4:21 am

  #886248

  Hello,

  Thanks for ur quick answer, works perfect... 🙂

  Best Regards

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  October 17, 2008 at 7:04 am

  #886327

  No Worries , Thanks for the feedback..