SQL MAX of multiple columns

Question

SQL MAX of multiple columns

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have this table:

ID    NUM      TIPO      CANTIDAD   FECHA 

451   225     36          60000     2018-05-16 

452   225     37          60000     2018-05-16 

453   225     38          00001     2018-05-07 

454   225     39          60000 2018-05-16 

455   225     40          00000      2018-05-07     
456   226     36          00001      2018-05-07     
457   226     37          00001     2018-05-07

458   226     38          00001      2018-05-07 

459   226     39          00001      2018-05-07     
460   226     40    00001        2018-10-28     
461   225     41    00001       2018-05-07  
462   226     41    00001       2018-05-07 

463   225     42    00001       2018-05-09

464   226     42    61000   2018-06-18 

465   225     36    60150   2018-11-17 


466   226     41  3       2018-11-17

and I want to obtain the maximum values of the column quantity for each TYPE and for each NUM, without repeating the types. (The ID is an automatic, it does not work for the consultation)

Thanks in advance

sql

asked by Luis 17.11.2018 в 20:56

source

2 answers

Help with ArrayList in Java Segmentation fault in Assembler 8086 NASM SASM?

score 2 · Answer 1

The two MAX can not be applied at the same time at least in the way you say: "Without repeating the type", or grouped by TIPO or by NUM , group by the two will inevitably repeat some TIPO

You can make two separate consultations:

SELECT TIPO, MAX(CANTIDAD)
       FROM TABLA
       GROUP BY TIPO

SELECT NUM, MAX(CANTIDAD)
       FROM TABLA
       GROUP BY NUM

score 1 · Answer 2

You could support yourself in the OVER clause for analytical queries (which are carried out in parallel with the FROM table) in the following way:

SELECT ID, 
      MAX(CANTIDAD) OVER (GROUP BY NUM) AS NUM, 
      MAX(CANTIDAD) OVER (GROUP BY TIPO) AS TIPO,
      CANTIDAD,
      FECHA
FROM TABLA

If you set the MAX () of each OVER, it will perform the same query that it does externally on the table but with certain specifications, in this case, those subqueries will be grouped by the column that would interest you respectively and then it would calculate the maximum of the AMOUNT column.

Theory itself if you need it: link