locked
to find all largest column with same column RRS feed

Answers

  • To not get f, do

    ;With cte As
    (Select id, name, age,
      Row_Number() Over(Partition By age Order by name Desc) As rn,
      Count(*) Over(Partition By age) As cnt
    From <Your table name>)
    Select id, name, age
    From cte
    Where rn = 1 And cnt > 1;

    Tom

    • Marked as answer by Oscar_Wu Friday, March 31, 2017 2:15 AM
    Friday, March 31, 2017 2:11 AM

All replies

  • If I understand what you are looking for, then

    ;With cte As
    (Select id, name, age,
      Row_Number() Over(Partition By age Order by name Desc) As rn
    From <Your table name>)
    Select id, name, age
    From cte
    Where rn = 1;

    Tom

    • Proposed as answer by Naomi N Thursday, March 30, 2017 3:36 PM
    • Unproposed as answer by Oscar_Wu Friday, March 31, 2017 1:32 AM
    Thursday, March 30, 2017 3:23 PM
  • Thank you Tom,

    I run your code, but the record name =f also be found. I just want two records that name =b and name=d.

    Friday, March 31, 2017 1:44 AM
  • To not get f, do

    ;With cte As
    (Select id, name, age,
      Row_Number() Over(Partition By age Order by name Desc) As rn,
      Count(*) Over(Partition By age) As cnt
    From <Your table name>)
    Select id, name, age
    From cte
    Where rn = 1 And cnt > 1;

    Tom

    • Marked as answer by Oscar_Wu Friday, March 31, 2017 2:15 AM
    Friday, March 31, 2017 2:11 AM
  • Thank you very much, I get the results. your code is right.
    • Edited by Oscar_Wu Friday, March 31, 2017 2:16 AM
    Friday, March 31, 2017 2:16 AM