SQL selects columns where other column is the Aggregation result

SELECT sum(ordertotal), userid,  username FROM orders GROUP BY userid;

Above query generates the infamous “Expression not in GROUP BY key” error, because the username column is not being aggregated but the ordertotal is.

An easy fix is to aggregate the username values using the collect_set function, but output only one of them: You should get the same output as before, but this time the username is included.

SELECT sum(ordertotal), userid, collect_set(username)[0] FROM orders GROUP BY userid;

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it.
Break up that resultset into partitions with the use of PARTITION BY.

SELECT userid, itemlist, sum(ordertotal) OVER (PARTITION BY userid)
FROM orders;

SELECT
  [ID],
  [State],
  [Value]
FROM
(
  SELECT 
    [ID],
    [State],
    [Value],
    ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Value] DESC) AS [Rank]
  FROM [t1]
) AS [sub]
WHERE [sub].[Rank] = 1
ORDER BY
  [ID] ASC,
  [State] ASC

Reference:
https://msdn.microsoft.com/en-CA/library/ms186734.aspx