There is a table in the database:

declare @Results Table ( ID int, TestResult5 int ) insert into @Results VALUES (1, 3), (1, 5), (2, 3), (2, 2), (2, 4)

enter image description here

It is necessary to remove records from the table, leaving the best student result. Those. after the SQL query should remain:

15

2 - 4

What should the query look like to solve this problem?

  • 2
    something like this: select id, max(колонка с оценкой) from table group by id - Bald
  • @Bald is not exactly that. I need to remove the rest. The fact is that in addition to the evaluation column there are others. - Adam
  • Those. you need not a sample, but a certain delete? I think this should be emphasized in the text of the question. And by the way, which of the records to keep, if the student has two entries with the maximum rating? - Mike
  • @Mike correctly noted. With the same оценка , the next column is the Первичный балл . Well, if this and that is the same, then you just need not touch. There will not be many of them and I can delete them manually. - Adam
  • @Bald tried to simplify the query. I also usually do. Next time I will consider. - Adam

1 answer 1

 delete A from @Results A where exists( select 1 from @Results B where B.id=A.id and B.TestResult5>A.TestResult5 );