Warren Step Warren Step - 4 months ago 9
MySQL Question

DBMS SQL Database Query

How to view this?

Problem: The oldest student per

sProgram
.

Table name is
Student
.

Columns are:

sID, sLast, sFirst, sMI, sProgram, sGender, sAge


Sample data:

sID sLast sFirst sMI sProgram sGender sAge
-------------------------------------------------------
001 Right Mc D BSIT M 26
002 Michael John G BSIT M 22
002 Franco James D BSCPE M 20
003 Step Ren D BSECE M 22


I want to display the oldest student of BSIT

The desired output is :

001 , Right, Mc, D, BSIT, M, 26

Answer

If you just want oldest student of 'BSID', you can try this:

SELECT *
FROM Student
WHERE sProgram = 'BSIT'
ORDER BY sAge DESC
LIMIT 1

Or if you want oldest student of each sProgram, you can do it like this:

SELECT t1.*
FROM Student t1
JOIN (
    SELECT sProgram, MAX(sAge) AS sAge FROM Student GROUP BY sProgram
) t2 ON t1.sProgram = t2.sProgram AND t1.sAge = t2.sAge
-- WHERE t1.sProgram = 'BSIT'
Comments