Thread: How to select rows that are the max for each subcategory?
Suppose I have a table T that has, among its columns, the fields X and Y, where Y is an integer, and multiple rows with the same value of X are possible. I want to select the rows corresponding to the greatest values of Y for each value of X. E.g. suppose that T is
X Y Z
a 1 eenie
a 3 meenie
a 2 miny
b 4 moe
b 0 catch
...the result of the desired query would be
a 3 meenie
b 4 moe
TIA!
Kynnjo
select x,y,z from t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) best wishes Harald On Thu, Sep 25, 2008 at 20:01, Kynn Jones <kynnjo@gmail.com> wrote: > Suppose I have a table T that has, among its columns, the fields X and Y, > where Y is an integer, and multiple rows with the same value of X are > possible. I want to select the rows corresponding to the greatest values of > Y for each value of X. E.g. suppose that T is > X Y Z > a 1 eenie > a 3 meenie > a 2 miny > b 4 moe > b 0 catch > ...the result of the desired query would be > a 3 meenie > b 4 moe > TIA! > Kynnjo > > > -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
uuuuuups, you need to specify the table in the select, so select t.x,t.y,t.z from t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) On Thu, Sep 25, 2008 at 20:05, Harald Armin Massa <haraldarminmassa@gmail.com> wrote: > select x,y,z > from > t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) > > best wishes > > Harald > > On Thu, Sep 25, 2008 at 20:01, Kynn Jones <kynnjo@gmail.com> wrote: >> Suppose I have a table T that has, among its columns, the fields X and Y, >> where Y is an integer, and multiple rows with the same value of X are >> possible. I want to select the rows corresponding to the greatest values of >> Y for each value of X. E.g. suppose that T is >> X Y Z >> a 1 eenie >> a 3 meenie >> a 2 miny >> b 4 moe >> b 0 catch >> ...the result of the desired query would be >> a 3 meenie >> b 4 moe >> TIA! >> Kynnjo >> >> >> > > > > -- > GHUM Harald Massa > persuadere et programmare > Harald Armin Massa > Spielberger Straße 49 > 70435 Stuttgart > 0173/9409607 > no fx, no carrier pigeon > - > EuroPython 2009 will take place in Birmingham - Stay tuned! > -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
"Kynn Jones" <kynnjo@gmail.com> writes:
> Suppose I have a table T that has, among its columns, the fields X and Y,
> where Y is an integer, and multiple rows with the same value of X are
> possible. I want to select the rows corresponding to the greatest values of
> Y for each value of X.
You could use DISTINCT ON --- see the "weather reports" example in the
SELECT reference page. It's not standard SQL but will usually be faster
than solutions that use only standard features.
regards, tom lane
Thank you all!
Kynn