0
I am doing a job in which I would like to show the maximum balance of each account type with subselect
within the SELECT
.
Something more or less like this:
CodConta ---------------------tipo ------------------ saldo maximo
2568468 Salario 6200
7895465 Corrente 9000
2315467 Poupança 32000
The table is called "account" and has the data:
- codconta
- guy
- balance
- Opening date
- Agency
I got something similar, but shows only the values and one for each column:
SELECT
MAX((SELECT saldo FROM conta WHERE tipo = 'Salario' AND codconta = s1.codconta)) "Conta salario",
MAX((SELECT saldo FROM conta WHERE tipo = 'Corrente' AND codconta = s1.codconta)) "Conta corrente",
MAX((SELECT saldo FROM conta WHERE tipo = 'Poupança' AND codconta = s1.codconta)) "Conta Poupança"
FROM(
SELECT
*
FROM conta
)s1
Table structure:
CREATE TABLE Conta (
CodConta NUMBER(6) NOT NULL PRIMARY KEY,
Tipo VARCHAR2(30) NOT NULL,
Saldo DECIMAL(12,2) NOT NULL,
Data_Abertura DATE,
Agencia NUMBER(5) NOT NULL,
FOREIGN KEY(Agencia) REFERENCES Agencia(NumAgencia)
)
need to be by sub-query itself? pq this is easy and much simpler using
group by
– Ricardo Pontual
Select type,max(balance) as saldomax from tabela group by tipo vide https://www.w3schools.com/sql/sql_groupby.asp
– Motta
I will have to think of another situation that forces me to use a subquery in select, as it is a job exercise asks for this
– Leonardo Hernandez