Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialStamos Bolovinos
4,320 PointsHow 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
- 01:15:00
- 02:05:00
- 09:05:00
- 09:55:00
- 10:44:00
- 11:35:00
- 12:25:00
How to solve this?
2 Answers
michalm
7,343 PointsPlayground is limited to certain extend - e.g. not supporting regex and advanced functions.
Edit: Correct me if I am wrong someone.
michalm
7,343 PointsYou 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! :)
Stamos Bolovinos
4,320 PointsThanks for the answer! So what do you mean with 'limited casting ability' - is this a limitation of only the Playground or general of SQLite?
Luis Marsano
21,425 PointsLuis Marsano
21,425 PointsI wouldn't say that's the problem. The problem is non-standard data. According to the SQLite manual, date-times should be in any of 3 particular formats:
The data in the
periods
table follows none of those standards. If it followed any of them, the data could be sorted and our time operating functions would work as designed.In practice, it'd be better to update the table with correctly formatted data. However, in this exercise, ordering by
periods.id
suffices.