CODEWITHSUNDEEP
mysqlsql
Preethy
Preethy

Reputation: 722

Sql query with order by not working

I want to join two tables namely staff and staff exam time tables. A staff can attend N number of Exams. My result should be staff id, staff name, end time of the last completed exam.

My Table structure is below

staff table

  staff_id  staff_full_name     staff_status

    500     Sakthi          active

    550     Siraj           active

    600     Shihab K H      active

    620     John David      active

    670     Javed Akthar        active

examtime table 

 examtime_id    examtime_staffid    examtime_endtime

    100     500         2014-10-10

    101     600         2016-05-01

    102     670         2016-06-10

    103     670         2014-04-01

    104     670         2016-06-13

    105     670         2016-06-11

SQL QUERY FOR THE RESULT SET IS BELOW

SELECT S.staff_id, S.staff_full_name, ET.examtime_endtime
FROM staffs S LEFT JOIN examtime ET ON ET.examtime_staffid = S.staff_id
WHERE 1 AND S.staff_status =  'active' GROUP BY S.staff_full_name ORDER BY S.staff_full_name ASC , ET.examtime_endtime DESC

But I am getting the result set as below. It is fetching the first record of the exam time table irrespective of the exam end time. See below Result set ( Javed Akthar Latest exam date is 2016-06-13 but it is fetching 2016-06-10 ).

500 Sakthi      2014-10-10

600 Shihab KH   2016-05-01

670 Javed Akthar    2016-06-10

Upvotes: 3

Views: 4011

Answers (2)

sagi
sagi

Reputation: 40481

The problem is with your GROUP BY clause, you didn't specify which date you want for each one, so it is randomly selecting one, not necessarly the maximum one.

You can find a good explanation about this behaviour here in @mjv answer

Try this query:

SELECT S.staff_id, S.staff_full_name, max(ET.examtime_endtime) as max_endTime
FROM staffs S 
LEFT JOIN examtime ET
 ON (ET.examtime_staffid = S.staff_id)
WHERE S.staff_status =  'active' 
GROUP BY S.staff_id ,
         S.staff_full_name 
ORDER BY S.staff_full_name ASC ,
         max_endTime DESC

It will select the maximum date for each staff_id and will order by it. In general - it is common to specify all the columns that represent each group(in this case staff_id,staff_full_name) in the GROUP BY clause and all other columns with an aggregation function(AVG/MAX/MIN..) , this will help you avoid this kind of problems in the future.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

If you just want the most recent exam time for each staff and don't require any other columns from that table, you can do this with standard SQL and avoid the hazard you've introduced by using a MySQL "extension" (wherein you can have columns that aren't in aggregates and aren't in the GROUP BY and MySQL selects an arbitrary value for that column):

SELECT MIN(S.staff_id) as staff_id,
       S.staff_full_name,
       MAX(ET.examtime_endtime) as examtime_endtime
FROM staffs S LEFT JOIN examtime ET ON ET.examtime_staffid = S.staff_id
WHERE S.staff_status =  'active'
GROUP BY S.staff_full_name

Upvotes: 1

Related Questions