CODEWITHSUNDEEP
mysql
Warren Step
Warren Step

Reputation: 25

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

Upvotes: 0

Views: 29

Answers (1)

Blank
Blank

Reputation: 12378

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'

Upvotes: 2

Related Questions