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

Re: [GENERAL] Can you get a whole row with a max on one column

From: Yury Don <yura(at)vpcit(dot)ru>
To: pgsql-general <pgsql-general(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] Can you get a whole row with a max on one column
Date: 2000-01-28 10:44:30
Message-ID: 0655.000128@vpcit.ru (view raw or flat)
Thread:
Lists: pgsql-general
Hello Scott,

Once, Friday, January 28, 2000, 12:26:10 AM, you wrote:

SVM> I have a table with these columns (and more but irrelevant ones)

SVM> | magnetic_susceptibility          | float8
SVM> | spin_coupling                    | float8
SVM> | boundary_coupling                | float8

SVM> I want a query to give (spin_coupling, boundary_coupling) where for each
SVM> boundary_coupling it gives the spin_coupling for which magnetic_susceptibility is
SVM> maximum.

SVM> Kinda like: 
SVM> select boundary_coupling, max(magnetic_susceptibility) from run_averages group by 
SVM> boundary_coupling; 

SVM> except I don't want to see max(magnetic_susceptibility) but the spin_coupling which
SVM> corresponds to max(magnetic_susceptibility).

SVM> I know I can do this with a small program, but I would like to have a single query
SVM> so that I can use psql -c "query" in a script and pipe the results to a file.

SVM> Thanks,

Perhaps
select a.spin_coupling, b.boundary_coupling from qq a
where a.magnetic_susceptibility=(select max(magnetic_susceptibility)
                         from qq b where b.boundary_coupling=a.boundary_coupling)

Best regards,
 Yury  ICQ 11831432
 mailto:yura(at)vpcit(dot)ru



In response to

pgsql-general by date

Next:From: Arnaud FLORENTDate: 2000-01-28 11:03:24
Subject: Re: [GENERAL]COPY still running
Previous:From: Thomas EggeDate: 2000-01-28 10:33:20
Subject: [Genweral] Problems by creating Triggers with postgrsql-6.5.3

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