CODEWITHSUNDEEP
postgresql
William Wino
William Wino

Reputation: 3819

Retrieve another field whose row is selected by an aggregate function

SELECT a.first_field, min(a.second_field), X 
FROM a
GROUP BY (a.first_field)

I want X to be a.third_field from the row that is selected by min(a.second_field) How do I do that?

Detailed explanation:

min(a.second_field) retrieves one minimum value from values (from rows) that are grouped by a.first_field. The value that is retrieved has to come from a row right? I want the query to also return another field value from the row that is selected by min function. Not only the minimum value of the field which is processed by min function.

Upvotes: 1

Views: 246

Answers (3)

valex
valex

Reputation: 24134

You should use ROW_NUMBER()

select first_field,second_field,third_field
from
(
SELECT a.*,
       ROW_NUMBER() 
         OVER (PARTITION BY a.first_field  
               ORDER BY second_field) 
        as rn
FROM a
) b where b.rn=1;

SQLFiddle demo

Upvotes: 2

Daniel Williams
Daniel Williams

Reputation: 8885

Try:

SELECT b.*, min(a.third_field) FROM (
SELECT a.first_field, min(a.second_field) 
FROM a
GROUP BY (a.first_field) ) as b
INNER JOIN a

Poorly formatted but it should work

Upvotes: 0

oxfn
oxfn

Reputation: 6840

I guess, you want to find third_value from row with minimum value of second_field. Then use this:

SELECT a.first_field, a.second_field, a.third_field
FROM a
WHERE a.third_field = MIN(a.third_field)
GROUP BY a.first_field

Upvotes: 0

Related Questions