[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] How to find the max value in a select?
From:       kumar1 () home ! com (Prasanth A !  Kumar)
Date:       2000-07-31 6:30:06
[Download RAW message or body]

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

[prev in list] [next in list] [prev in thread] [next in thread]