0

I need to write a query in SQL Server that gets the max value of a certain column for each group along with the corresponding value of another column.

This is a simplification of my case:

group  | value | other_value
-------+-------+------------
First  |    5  |     2
First  |   12  |     4
First  |    7  |     7
Second |   12  |    43
Second |   45  |    12
Third  |   45  |     7

Result of query :

group  | max_value | other_value
-------+-----------+------------
First  |    12     |     4
Second |    45     |    12
Third  |    45     |     7
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user123
  • 387
  • 1
  • 4
  • 13
  • use row_number for this – Lamak Jan 18 '19 at 16:21
  • @Lamak can you elaborate more please ? – user123 Jan 18 '19 at 16:24
  • 3
    @CetinBasoz not really "unfortunately", since the answer there fits perfectly for this question – Lamak Jan 18 '19 at 16:26
  • select t1.* from myTable t1 inner join (select [group], max(max_value) as maxVal from mytable group by [group]) t2 on t1.[group] = t2.[group] and t1.max_value = t2.maxVal; – Cetin Basoz Jan 18 '19 at 16:27
  • @Lamak, it maybe not unfortunate but disrespectful while others writing replies. And "answer" where? – Cetin Basoz Jan 18 '19 at 16:28
  • 4
    @CetinBasoz why would closing the question as a duplicate of an exact duplicate would be disrespectful?. The whole point of closing a question as a duplicate is to not let it get answers, since it's already answered on the site. If you scroll to the top, you'll see the link to the duplicated question, and the answers there – Lamak Jan 18 '19 at 16:30
  • @Lamak, you are saying it is duplicate and it should be. In theory there isn't any questions that doesn't have an answer on the internet and on SO in particular, so all questions are already duplicates. So should be closed to new questions with this mindset. Second, why would somebody, be it the poster or the one who wants to answer, have to chase for the question's duplicate? For example how would I know if it were already answered? And how do I know if existing answer is the best, doesn't have any alternative etc? Also I don't see a link to answer, do you? – Cetin Basoz Jan 18 '19 at 16:34
  • 3
    @CetinBasoz I don't get what you are talking about. Of course the op should at least try to find if his/her question is already answered somewhere. And even if they don't, **someone else** marked it as a duplicate...which gives the answer. What's your point?. And yes, **there is** a link to the question that's already answered, you need to scroll up – Lamak Jan 18 '19 at 16:37
  • @Lamak, I later saw the link at top and checked. Question and answer was similar but not an exact duplicate so this should not be closed. My point is clear, I find it disrespectful, someone closes a question while I am writing an answer to it! We shouldn't have a need for chasing answers before replying. – Cetin Basoz Jan 18 '19 at 16:38
  • 4
    @CetinBasoz yes, we should. Of course it's not an **exact** duplicate, but it's the same question. SO is not a "write me an answer" service, it's built for the community, and it's good that users don't let the same questions get the same answers again and again. It's a feature that we can't close questions as duplicates, to reduce noise – Lamak Jan 18 '19 at 16:44
  • @Lamak, OK I agree to disagree. – Cetin Basoz Jan 18 '19 at 16:45

0 Answers0