CODEWITHSUNDEEP
sqlselectsqlite
Daniel Mendonça
Daniel Mendonça

Reputation: 401

SQLITE3 Sub-querys

I'm having a problem, that i can't figure it out, even after reserching here and at sqlite.org

So, I have these tables:

CREATE TABLE MEDICO(
    idMedico INTEGER PRIMARY KEY AUTOINCREMENT,
    nome VARCHAR(50) NOT NULL,
    morada VARCHAR(50) NOT NULL,
    telefone VARCHAR(9) NOT NULL
);



CREATE TABLE PRESCRICAO(
    idPrescricao INTEGER PRIMARY KEY AUTOINCREMENT,
    idConsulta INTEGER,
    idMedico INTEGER NOT NULL,
    nrOperacional INTEGER NOT NULL,
    FOREIGN KEY(idConsulta) REFERENCES CONSULTA(idConsulta),
    FOREIGN KEY(idMedico) REFERENCES MEDICO(idMedico),
    FOREIGN KEY(nrOperacional) REFERENCES UTENTE(nrOperacional)
);


CREATE TABLE PRESCRICAO_MEDICAMENTO(
    idPrescricao INTEGER ,
    idMedicamento INTEGER,
    nrEmbalagens INTEGER NOT NULL,
    FOREIGN KEY(idPrescricao) REFERENCES PRESCRICAO(idPrescricao),
    FOREIGN KEY(idMedicamento) REFERENCES MEDICAMENTO(idMedicamento),
    PRIMARY key(idPrescricao, idMedicamento)
);

I want the idMedicamento that is the most used by the MEDICO lets say with idMedico=7, until here, everything's fine, i'm doing:

  SELECT idmedicamento, MAX(total) as maximum
    FROM (SELECT idMedicamento, COUNT(idMedicamento) AS total
        FROM PRESCRICAO_MEDICAMENTO
        WHERE PRESCRICAO_MEDICAMENTO.idPrescricao IN (
            SELECT idPrescricao FROM PRESCRICAO
            WHERE PRESCRICAO.idmedico= 7
        )
        GROUP BY idMedicamento);

and i get:

IDmedicamento:3 maximum:5

wich is something that I want and it is correct.

but when i do:

SELECT idMedicamento
FROM (SELECT idMedicamento, MAX(total) as maximum
FROM (SELECT idMedicamento, COUNT(idMedicamento) AS total
    FROM PRESCRICAO_MEDICAMENTO
    WHERE PRESCRICAO_MEDICAMENTO.idPrescricao IN (
        SELECT idPrescricao FROM PRESCRICAO
        WHERE PRESCRICAO.idmedico= 7
    )
    GROUP BY idMedicamento));

All i get is the last used idMedicamento by the MEDICO, in this case, MEDICAMENTO with idMedicamento=5.

Any idea what i'm doing wrong? Really can't figure it out.

Thanks

Upvotes: 1

Views: 93

Answers (2)

CL.
CL.

Reputation: 180020

In many cases, the easiest way to get other columns from the record with a maximum value is to use ORDER BY/LIMIT 1:

SELECT idMedicamento
FROM PRESCRICAO_MEDICAMENTO
WHERE idPrescricao IN (SELECT idPrescricao
                       FROM PRESCRICAO
                       WHERE idmedico = 7)
GROUP BY idMedicamento
ORDER BY COUNT(*) DESC
LIMIT 1

Upvotes: 1

Marc Shapiro
Marc Shapiro

Reputation: 571

The second query is wrong because the first query is wrong; I don't think it's valid SQL, and I'm not sure why it's working for you.

The inner query (which I'll call Q1) of the first query is ok: SELECT id, COUNT(id) AS total FROM ... GROUP BY id;

But the outer query of the first query is broken: SELECT id, MAX(total) FROM ...; without a GROUP BY. This is wrong because the MAX forces an aggregation over the entire table (which is what you want), but the 'id' is not aggregated.

If you remove 'id, ' from the query, you should correctly get the maximum: SELECT MAX(total) AS maximum FROM ...; which I'll call Q2.

Then it gets ugly, because SQLite doesn't support CTEs. Basically it is:

SELECT id FROM (Q1) WHERE total = (Q2);

but you have to write out Q1 and Q2, and there's a lot of repetition because Q2 includes Q1.

Upvotes: 0

Related Questions