Suppose I have the following table T: a b ——– 1 abc 1 def 1 ghi 2 jkl 2 mno 2 pqr And I do the following query: SELECT a, b FROM T GROUP BY a The output should have two rows, one row where a=1 and a second row where a=2. But what should … Read more
The second half of the currently accepted answer is outdated and has two deprecations. First and most important, you can no longer pass a dictionary of dictionaries to the agg groupby method. Second, never use .ix. If you desire to work with two separate columns at the same time I would suggest using the apply … Read more
[*] Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*]. To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you’d need to show: SELECT m.cname, m.wmname, t.mx FROM ( SELECT … Read more
They’re used in different places. GROUP BY modifies the entire query, like: select customerId, count(*) as orderCount from Orders group by customerId But PARTITION BY just works on a window function, like ROW_NUMBER(): select row_number() over (partition by customerId order by orderId) as OrderNumberForThisCustomer from Orders GROUP BY normally reduces the number of rows returned … Read more
At first glance… All you need is a GROUP BY clause with the MAX aggregate function: SELECT id, MAX(rev) FROM YourTable GROUP BY id It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row … Read more