CODEWITHSUNDEEP
mysqlsql
JanTay
JanTay

Reputation: 1

order by in sql join

I have an sql query which returns exactly the data I require. However, I'm having trouble with the Order By. I think I may need some sort of nested select or a Group By but I have never done one of those, so I wondered if someone could assist. The data needs to be sorted firstly by centre_id, then by learner_name and then by journey_centre_start but it only seems to sort by centre_id and learner_name and ignores the sort for journey_centre_start.

Journey_centre_start is a date type with a NULL default value in the database.

SELECT a.learner_id
     , a.learner_name
     , a.centre_id
     , a.learner_status
     , b.journey_id
     , c.journey_centre_start
  FROM learner a
  JOIN learner_journey b  
    ON a.learner_id = b.learner_id
  JOIN journey c 
    ON b.journey_id = c.journey_id
 WHERE a.centre_id != 999
   AND a.learner_status = 'complete'
 ORDER 
    BY a.centre_id ASC 
     , a.learner_name ASC 
     , c.journey_centre_start ASC;

Upvotes: 0

Views: 68

Answers (2)

JanTay
JanTay

Reputation: 1

Thank you to everyone who responded to my post. By talking it through it actually dawned on me that the solution was quite simple. I just needed to change round the Order By statement to: ORDER BY a.centre_id ASC, c.journey_centre_start ASC, a.learner_name ASC and it gave me the result I wanted. Kindest regards to you all.

Upvotes: 0

CodeKeeper55
CodeKeeper55

Reputation: 11

If you want the NULL values in journey_centre_start to be at the bottom of the list then you can add a CASE statement to your ordering`

  SELECT a.learner_id
 , a.learner_name
 , a.centre_id
 , a.learner_status
 , b.journey_id
 , c.journey_centre_start
  FROM learner a
 JOIN learner_journey b  
  ON a.learner_id = b.learner_id
 JOIN journey c 
  ON b.journey_id = c.journey_id
 WHERE a.centre_id != 999
 AND a.learner_status = 'complete'
 ORDER 
 BY a.centre_id ASC 
 , a.learner_name ASC 
 , CASE WHEN c.journey_centre_start IS NULL THEN 2 ELSE 1 END
 , c.journey_centre_start ASC

Upvotes: 1

Related Questions