Menu
Consider the following SQL Server table:
ID | X | Y
------+-------+-------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 40
5 | 2 | 500
6 | 3 | 1
7 | 3 | 100
8 | 3 | 10
I need to select the ID of the row that has the maximum value of Y grouped by x, i.e:
ID | X | Y
------+-------+-------
3 | 1 | 3
5 | 2 | 500
7 | 3 | 100
The query will be nested several times so an optimal performance solution is required...
847
#group by store and sort by sales values in ascending order df.sort_values( ['store','sales']).groupby('store').head() store sales 2 A 8 3 A 14 6 A 30 7 A 30 4 B 10 0 B 12 5 B 20 1 B 25 Note that the head () function only displays the first 5 values by group. To display the top n values by group, simply use head (n) instead.
Only rows where the salesperson is not Bennett are considered. Use the ORDER BY clause to display the output table of a query in either ascending or descending alphabetical order. Whereas the GROUP BY clause gathers rows into groups and sorts the groups into alphabetical order, ORDER BY sorts individual rows.
We can use the following syntax to group the rows by the store column and sort in descending order based on the sales column: #group by store and sort by sales values in descending order df.sort_values( ['store','sales'],ascending=False).groupby('store').head() store sales 1 B 25 5 B 20 0 B 12 4 B 10 6 A 30 7 A 30 3 A 14 2 A 8
Sort within Groups of groupby () Result in DataFrame By using DataFrame.sort_values (), you can sort DataFrame in ascending or descending order, before you use this first group the DataFrame rows by using DataFrame.groupby () method. Note that groupby preserves the order of rows within each group.
Setup:
declare @MyTable table(ID int, X int, Y int)
insert @MyTable
values
( 1 , 1 , 1),
( 2 , 1 , 2),
( 3 , 1 , 3),
( 4 , 2 , 40),
( 5 , 2 , 500),
( 6 , 3 , 1),
( 7 , 3 , 100),
( 8 , 3 , 10)
Query:
;with cte
as
(
select *, row_number() over(partition by X order by Y desc) RowNumber
from @MyTable
)
select Id, X, Y
from cte
where RowNumber = 1
Result:
Id X Y
----------- ----------- -----------
3 1 3
5 2 500
7 3 100
The query is for MS SQL 2005+. The setup will work in MS SQL 2008+.
59
Yeah, many times this type of question... Added keyword "STRAIGHT_JOIN" to pre-optimize the query by enforcing the "PreQuery" first.
select STRAIGHT_JOIN
YT.*
from
( select x, max(y) HighPerX
from YourTable
group by x ) PreQuery
join
YourTable YT
on PreQuery.X = YT.X
AND PreQuery.HighPerX = YT.y
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
