none
Selecting the maximum value of a column

    Question

  • Hi,

    Let's say I have a U-SQL rowset called @data with 2 columns: a and b

    What I want is to get a new rowset @data2 with the same column a, but I want every entry in @data2's column b to be the maximum value @data's column b.

    I tried to do this: @data2 = SELECT a, MAX(b) AS b FROM @data

    But this gives me the error: "Every element in a select item must match an expression in GROUP BY exactly unless it is a constant or aggregate function."

    How can I solve this? Thanks.


    • Edited by aquadz0 Tuesday, July 18, 2017 9:00 PM
    Tuesday, July 18, 2017 8:59 PM

All replies

  • Is this what you're looking for?
    @data2 =
        SELECT aa.a, bb.b
        FROM @data AS aa
        CROSS JOIN (SELECT b FROM @data ORDER BY b DESC FETCH 1 ROW) AS bb;


    Thursday, July 20, 2017 4:50 PM
  • Yes, thank you. How could I extend this if, instead of just 1 column b I had many more?

    So if there was a column a, b, and c in @data, I would like @data2 to have the same column a, every entry in column b should be the maximum value in b, and every entry in column c should be the maximum value in column c.

    Any tips would help!

    Monday, July 24, 2017 8:33 PM