0

In SQL Server, I have a table that includes a list of duplicate tasks. Those tasks have the same id and name, but each one of them has a different value in a third column. How can I pick duplicates that have the higher value in third column?

For example, I have:

task_id task_name third_column
1 task1 4
1 task1 2
2 task2 5
2 task2 7

I would like to have this result:

task_id task_name third_column
1 task1 4
2 task2 7

Please keep in mind I would like to retrieve all the columns. I have been trying to make the MAX function work, and I am able to get a list grouped by a specific column and that excludes the info from other columns, where I would like to keep info from each column.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Gregory
  • 1
  • 1
  • I read this as the OP wants everything *but* the "first" row, @MarkRotteveel , not they want the first row. Which would mean `> 1` not `= 1`. – Thom A Mar 10 '22 at 16:41
  • @Larnu They want the row with the highest value for third_column where task_id and task_name are equal. – Mark Rotteveel Mar 10 '22 at 16:44
  • *"How can I pick duplicates that have the higher value in third column?"* "higher" not "highest" @MarkRotteveel . So I read that as "anything but the lowest". – Thom A Mar 10 '22 at 16:46
  • @Larnu In this context, I read "the higher value" as the highest value among all rows in the group (as in, when comparing row by row, you always choose the higher, so you end up with the highest). But even if that interpretation is not correct, the solutions that work for retrieving the highest value can generally be modified to exclude only the lowest value. – Mark Rotteveel Mar 10 '22 at 16:50
  • Which is why I mentioned they would need `> 1` is my interpretation is correct.. – Thom A Mar 10 '22 at 16:51
  • Yes, @MarkRotteveel is correct. Just like in my example table, there are always pairs of duplicates, where the only difference between them is the value of the third_column. I wanted to remove the ones from each pair that have the highest value. Thank you for pointing out that a similar question was asked. I am still new to posting, so I couldn't exactly find a similar example. – Gregory Mar 10 '22 at 22:46

0 Answers0