Skip site navigation (1) Skip section navigation (2)

Re: How to select rows that are the max for each subcategory?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kynn Jones" <kynnjo(at)gmail(dot)com>
Cc: "pgsql-general General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to select rows that are the max for each subcategory?
Date: 2008-09-25 18:12:35
Message-ID: 9717.1222366355@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Kynn Jones" <kynnjo(at)gmail(dot)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

In response to

Responses

pgsql-general by date

Next:From: Casey Allen ShobeDate: 2008-09-25 18:14:36
Subject: Re: Oracle and Postgresql
Previous:From: zach cruiseDate: 2008-09-25 18:09:13
Subject: ease of use sync

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group