Stamos Bolovinos4,320 Points
How to sort on start_time?
I don't agree to the solution of the teacher. I remember from my school time, what a schedule should look like. First each class has a number, so I used the pk of the classes table. Then a schedule contains the class name and the teacher. Of course it needs to have the start time, and room number, so we know when and where to go. And the schedule of course needs to be sorted by time.
The problem I'm facing is that the periods table stores the START_TIME as text.
I can't figure out how to sort on this column.
I tried this:
SELECT c.id 'Class ID', su.name 'Class Name', su.grade 'Grade', t.first_name || ' ' || t.last_name 'Teacher', CASE WHEN LENGTH(p.start_time) = 5 THEN TIME(p.start_time) ELSE TIME('0' || p.start_time) END 'Start Time', r.id 'Room Number' FROM classes c INNER JOIN schedule sc ON c.id = sc.class_id INNER JOIN students st ON sc.student_id = st.id INNER JOIN teachers t ON c.teacher_id = t.id INNER JOIN subjects su ON c.subject_id = su.id INNER JOIN periods p ON c.period_id = p.id INNER JOIN rooms r ON c.room_id = r.id WHERE st.first_name = 'Rex' AND st.last_name = 'Rios' ORDER BY 'Start Time'
But it delivers unexpected results in the sorting of the 'Start Time' column. I know the result would still be incorrect, as AM and PM information is also missing.
But still I would expect from my query to get times sorted as
How to solve this?
Playground is limited to certain extend - e.g. not supporting regex and advanced functions.
Edit: Correct me if I am wrong someone.
You will not be able to sort by START_TIME this particular time. START_TIME value is stored as String and as far as I am aware SQL Playground has limited casting ability (in RL situation we would just TO_DATE it).
Nevertheless, in PERIODS table each period has it's ID assigned according to START_TIME which means you will be good to go as long as you order on ID.
Side note: INNER JOIN can be simply replaced with JOIN, they are equal. Of course in complex queries it will make everything clearer (especially when you have heaps of LEFT / RIGHT JOINS). Also don't bother using FIRST and LAST_NAME, just go with Rex's ID in WHERE.
Apart from that - excellent work Sir! :)