Re: How to find the max value in a select? - Mailing list pgsql-general

From kumar1@home.com (Prasanth A. Kumar)
Subject Re: How to find the max value in a select?
Date
Msg-id m34s56vmkx.fsf@C654771-a.frmt1.sfba.home.com
Whole thread Raw
In response to How to find the max value in a select?  (Erich <hh@cyberpass.net>)
List pgsql-general
Erich <hh@cyberpass.net> writes:

> I need to do something like this:
>
> SELECT ...
> FROM ...
> WHERE ...
> ORDER BY ...
> COUNT 1
>
> In other words, I want to find the one row matched by my WHERE clause
> which is the maximum or minimum of all the rows that matched it.  I
> could do the query above (ORDER BY...   COUNT 1), but does Postgres
> optimize this, or does it find all the rows, sort them, and then take
> off the top one?  Or is there some better way to do it?
>
> Thanks,
>
> e

SELECT <other_cols>, max(<col_c>)
    from <table> where <condition>
        group by <other_cols>
            order by <other_cols>;

Basically you use an aggregate operator max() or min(). If you are
selecting other columns at the same time, then you need to group by
them and optionally order by them for it to make sense.

I don't know if this is necessarily faster in postgres but it is a
standard sql feature instead of the count 1.

--
Prasanth Kumar
kumar1@home.com

pgsql-general by date:

Previous
From: Erich
Date:
Subject: How to find the max value in a select?
Next
From: Nina Kuznetsova
Date:
Subject: gcc-version