How to get rows with the greatest value in one column?

Question

I have this query in Postgres:

SELECT DISTINCT P.nome, P.cognome, F.nomeFacolta, F.id, D.orelez
FROM Persona P, Docenza D, InsErogato IE, Facolta F
WHERE D.id_inserogato = IE.id
    AND IE.id_facolta = F.id
    AND D.id_persona = P.id
    AND D.orelez = ANY ( SELECT MAX(D2.orelez)
        FROM Facolta F2, Docenza D2, Inserogato IE2
        WHERE D2.id_inserogato = IE2.id
            AND IE2.id_facolta = F2.id
            AND IE2.annoaccademico = '2009/2010'
        GROUP BY F2.id)
ORDER BY D.orelez DESC;

With this result:

nome            | cognome       | NomeFacolta    | id | orelez |

Francesco         Bortolan        Medicina         7    128000  <---
Giampaolo         Dalle Vedove    Economia         2    98000   <---
Mauro             Spera           Scienze          1    87000   <---
Alessandra        Salomoni        Economia         2    80000
Alessandro        Natucci         Economia         2    80000
Attilio           Boner           Medicina         7    80000
Mario Rosario     Buffelli        Lettere          8    76000   <---
Carlo             Capelli         Lettere          8    72000
etc ...

I want to change or integrate my query to take only the MAX orelez value for id (I indicated with an arrow the tuples that I want to keep in the result). Also I want my query takes only the first three attributes, discarding the last two.

This is the desired output for above example:

nome            | cognome       | NomeFacolta    

Francesco         Bortolan        Medicina    
Giampaolo         Dalle Vedove    Economia    
Mauro             Spera           Scienze      
Mario Rosario     Buffelli        Lettere     
etc ...

How can I change or integrate my query to do this?

score 0 · Answer 1 · answered Jan 04 '17 at 16:42

As far as you do not want to show id and orelez, use a subquery that group it by id, and then show the desired columns.

SELECT R.nome, R.cognome, R.nomeFacolta
FROM (SELECT 
          F.id, P.nome, P.cognome, F.nomeFacolta, MAX(orelez) as orelez
      FROM 
          Persona P, 
          JOIN Docenza D ON D.id_persona = P.id, 
          JOIN InsErogato IE ON D.id_inserogato = IE.id, 
          JOIN Facolta F ON IE.id_facolta = F.id
      WHERE
          IE2.annoaccademico = '2009/2010'
      GROUP BY 
          F.id, P.nome, P.cognome, F.nomeFacolta) R
ORDER BY 
    R.orelez DESC;

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

The query you have is most probably incorrect. The predicate:

WHERE  D.orelez = ANY (SELECT MAX(D2.orelez) ...

would include any row that happens to have the same orelez as the maximum for any id, not just the same id.

You could repair with:

 WHERE (F.id, D.orelez) = ANY (SELECT F2.id, MAX(D2.orelez) ...

But there is more. Only the subquery is restricted to IE2.annoaccademico = '2009/2010'. The outer query does not have the same restriction, so it will return rows with any annoaccademico, as long as orelez is matched. You would have to repeat the predicate. It's possible you really want to find:

All rows that match the maximum of orelez for any id with annoaccademico = '2009/2010'.

But that seems rather unlikely. Assuming you really want to find:

One single row per id with the maximum orelez and annoaccademico = '2009/2010'

This can be radically simplified with DISTINCT ON:

SELECT DISTINCT ON (F.id)
       P.nome, P.cognome, F.nomeFacolta
FROM   Persona    P
JOIN   Docenza    D  ON D.id_persona = P.id
JOIN   InsErogato IE ON IE.id = D.id_inserogato
JOIN   Facolta    F  ON F.id = IE.id_facolta
WHERE  IE.annoaccademico = '2009/2010'
ORDER  BY F.id, D.orelez DESC;

Detailed explanation and instructions for possible corner cases with NULL values and how to break ties:

Select first row in each GROUP BY group?

Debug instructions

Typical reasons why that might not produce expected results:

There are more rows with the same max. value in D.orelez. (Do you want to return all of them? Then you need a different query.)
The max. value in D.orelez is not for annoaccademico = '2009/2010'.
Row may be eliminated by the joins, not finding a match in IE or F.
There are NULL values in D.orelez - add NULLS LAST like instructed in the linked answer.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 04 '17 at 17:59

Erwin Brandstetter

605,456
145
1,078
1,228

thanks, it show me 8 row, but non with the max for each id. i would the teacher with MAX "orelez" for each id. in your query there is only a descending order, but not a max selection for id. i'm working on my university database and i can check, for example, that the teacher with max orelez for id 7 is Francesco Bortolan with 128000 "orelez" value. i test your query and for the same id it result another name (Domenico De Leo), and if i put ASC the name change (in Giorgio Talamini). – James The Beard Jan 05 '17 at 11:05
This is a further proof that the result is incorrect even though it is very close to the solution. the only thing we must do, i think, is to add the MAX operator somewhere in your query to make it work , but i do not know where. do you understand on what i want to see in that query? – James The Beard Jan 05 '17 at 11:05
@JamesTheBeard: We do not need `max()`. The `ORDER BY` clause takes care of that. This query technique is proven good. Possible explanations why teacher 'Francesco Bortolan' is not in the result: **1.** There is another teacher with the same value *128000* for `orelez` - do you want to return both in this case? **2.** The value *128000* is not for `annoaccademico = '2009/2010'`. **3.** The row is eliminated by the joins because there is no related row in `IE` or `F`. **4.** There are NULL values in `D.orelez` - add `NULLS LAST` like instructed in the linked answer. – Erwin Brandstetter Jan 05 '17 at 16:07
ok we don't need max, i understand. but 1. i have checked and i have seen that there is only one teacher ('Francesco Bortolan') wiith 128000 orelez for the id 7. 2. it can be possible. 3. i rule out this possibility. 4. yes, there are NULL values in D.orelez. i try to do that. I will let you know if there will be changes in the result or not. – James The Beard Jan 06 '17 at 09:34
i just tried and it works with NULLS LAST, you are a genius, thank you !!! – James The Beard Jan 06 '17 at 09:44

How to get rows with the greatest value in one column?

2 Answers2

Debug instructions